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 calculate the sum, max, min, average and percentage from each category.

Can someone please help me with the measures?

thanks a lot, Mel

5 Replies

  • 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?