Forum Discussion

MBreden's avatar
MBreden
Icon for Helper I rankHelper I
3 years ago

Calculations within categories

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 calcul...
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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:

 

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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: