Forum Discussion
How to do exception aggregation in Power BI?
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?
Anonymous
Please try
Max Amount =
SUMX (
VALUES ( 'Table'[YearMonth] ),
MAXX (
CALCULATETABLE ( VALUES ( 'Table'[Expense Item] ) ),
CALCULATE ( SUM ( 'Table'[Amount] ) )
)
)
4 Replies
- tamerj1
Community Champion
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
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
Anonymous
Please try
Max Amount =
SUMX (
VALUES ( 'Table'[YearMonth] ),
MAXX (
CALCULATETABLE ( VALUES ( 'Table'[Expense Item] ) ),
CALCULATE ( SUM ( 'Table'[Amount] ) )
)
)