Calculations within categories

[MBreden]

Hi all,

In my PBI table, values are calculated by category and retailer.

Value = SUM('Data'[TradeTerm Value])
'Dim Retailer'[Retailer ID]
'Dim Category'[Main]

I would now like to calculate the sum, max, min, average and percentage from each category.

Can someone please help me with the measures?

thanks a lot, Mel

[FreemanZ]

hi @MBreden 

not sure if i fully get you, you may try to plot a table visual with necessary columns and measures like:

Sum = 
SUMX( //replacing SUMX with MINX, MAXX, AVERAGEX for other measures
    FILTER(
        ALL(TableName),
        TableName[Categorie] = MAX(TableName[Categorie])
    ),
    TableName[Value]
)

TT% = 
DIVIDE(
    SUM(TableName[Value]),
    [Sum]
)

it worked like:

[MBreden]

Hi @FreemanZ ,

thanks for your reply, but I'm not getting a proper result yet.
Shouldn't the table 'Dim Retailer' also be taken into the measure

[FreemanZ]

hi @MBreden 

[Main] and measure [TT Value], what are they? How are they related with the columns in your original post?

[MBreden]

Hi @FreemanZ,

I could not implement your 2nd suggestion at all.

My data model looks like this:

For better understanding, I have created a shortened example file.

[FreemanZ]

hi @MBreden 

if you mind the duplicates, try like:

Sum2 = 
VAR _table = 
FILTER(
    ALL(TableName),
    TableName[Categorie] = MAX(TableName[Categorie])
)
VAR _id =MAXX( _table,TableName[Retailer ID]
)
RETURN
IF(
    MAX(TableName[Retailer ID]) = _id, 
    SUMX( _table, TableName[Value]) //replacing SUMX with MINX, MAXX, AVERAGEX
)

it worked like: