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:

     

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

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

      hi MBreden 

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

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

        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.