topic Re: How to do exception aggregation in Power BI? in DAX Commands and Tips

How to do exception aggregation in Power BI?

Anonymous — Mon, 15 May 2023 14:34:55 GMT

Hello. I need something like this. You can compare the article by looking at the picture above;

I have a table with columns material type, calendar month/year, expense item, quantity.
For each material type [I'm only showing one (P) in the example], I want to return, on a monthly basis, the value with the highest cost item amount for that month and add the returned values to get a result total.

So the highest cost item value for January is 50.
The highest cost item value for February is 11
Highest cost item value for March 1400

When I add these the total result is 1461
This is what I want to do and want to see in Power BI.

However, although I tried various formulas in the Power BI section, I can't get the highest cost item value for that month and get a total result as I want. Can you help me?

Re: How to do exception aggregation in Power BI?

tamerj1 — Mon, 15 May 2023 16:43:44 GMT

Hi Anonymous

please try

Expense Amount =
IF (
ISINSCOPE ( 'Table'[Expense Item] ),
SUM ( 'Table'[Amount] ),
CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Expense Item] = "Dogalgaz" )
)

please make sure to type Dogaldaz with the Turkish characters

Re: How to do exception aggregation in Power BI?

Anonymous — Tue, 16 May 2023 06:38:38 GMT

Thank you for the answer.

However, while the item with the highest cost item value for January and February is "Dogalgaz", the item with the highest cost item value for March is "Enerji". Therefore, this formula did not return a correct value.

I need the formula that returns the highest cost item value for that month and then sums these highest values.

thanks again

Re: How to do exception aggregation in Power BI?

tamerj1 — Tue, 16 May 2023 07:10:09 GMT

Anonymous

Please try

Max Amount =
SUMX (
VALUES ( 'Table'[YearMonth] ),
MAXX (
CALCULATETABLE ( VALUES ( 'Table'[Expense Item] ) ),
CALCULATE ( SUM ( 'Table'[Amount] ) )
)
)

Re: How to do exception aggregation in Power BI?

Anonymous — Tue, 16 May 2023 11:29:36 GMT

thanks a lot. I caught the correct values in the breakdowns with this formula, but the result total value returns less value than it should, for some reason I don't understand. What do you think could be the reason?

I exported the table in Power BI to excel, I did not make any changes, the number of materials and the maximum amount are exactly the same. Then the results look essentially the same

------------------------------------------------------------------------------------