topic Re: Calculations within categories in DAX Commands and Tips

Calculations within categories

MBreden — Wed, 05 Apr 2023 13:10:45 GMT

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

Re: Calculations within categories

FreemanZ — Wed, 05 Apr 2023 14:07:21 GMT

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:

Re: Calculations within categories

FreemanZ — Wed, 05 Apr 2023 14:15:43 GMT

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:

Re: Calculations within categories

MBreden — Wed, 05 Apr 2023 15:03:22 GMT

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

Re: Calculations within categories

FreemanZ — Fri, 07 Apr 2023 01:38:35 GMT

hi MBreden

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

Re: Calculations within categories

MBreden — Tue, 11 Apr 2023 10:55:42 GMT

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.