Weird Sum DAX help...

[Matty]

Hi Team,

Looking for some DAX to achieve the following...

Data as follows:
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>

Shop	Type	Week	Amount
A	Bag	1	10
A	Bag	2	15
A	Bag	3	12
A	Box	1	10
A	Box	2	15
A	Box	3	12
B	Bag	1	5
B	Bag	2	10
B	Bag	3	10
C	Bag	1	20
C	Bag	2	20
C	Bag	3	20

<tbody>
</tbody>

Result as follows:
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>

Week	Amount
1	35
2	45
3	42

<tbody>
</tbody>

The result was achieved as follows:

Where more than one Type exists for a given Shop / Week combination, take the MAX Amount for that Shop / Week across ALL Types, otherwise just take the Amount. Finally, Sum the resulting values up for each Week.

I've had a play myself but can't quite get it, hence the ask.

Cheers,

Matty

 

[Ozeroth]

Hi there Matty,

I would do something like this:

Weird Sum =
SUMX (
    SUMMARIZE ( Sales, Sales[Week], Sales[Shop] ),
[COLOR=#ff0000][B]    CALCULATE ([/B][/COLOR]
        MAXX ( VALUES ( Sales[Type] ), CALCULATE ( SUM ( Sales[Amount] ) ) )
[COLOR=#ff0000][B]    )[/B][/COLOR]
)

It basically says, for each Week/Shop combination, take the maximum Amount (sum) across Types.
If there is only one Type, then it is the maximum by default.

The red CALCULATE can be omitted and you still get the same result, but it may improve performance, as it ensures the MAXX iterates only over Types that exist for each Week/Shop combination.

Does this work for you?

Cheers,
Owen

 

[Matty]

Thanks Owen - that did the trick.

Cheers,

Matty