Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 

    Please try

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Anonymous 

Please try

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

     

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