How to do exception aggregation in Power BI?

[sameteskb]

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?

[tamerj1]

Hi @sameteskb 

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 

[sameteskb]

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

[tamerj1 · Accepted solution]

@sameteskb 

Please try

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

[sameteskb]

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

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