Forum Discussion

anoomohan's avatar
anoomohan
Icon for Helper III rankHelper III
1 year ago
Solved

TOTAL CATEGORY VS SUBCATEGORY

HI ALL,

 

Please help - i have 2024 projected sales for 3 countires , 5 segments per country. I have 2025 budget as % increment per country per segment. 

 

I added a measure to calculate 2025 budget value by adding 2024 projected sales + 2025 budget value - its coming correct for segment , but total for country is not matching with the subcategory total. please help me. 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from ryan_mayu  and Greg_Deckler , please allow me to provide another insight:

    Hi  anoomohan ,

    I created some data:

    Are you referring to the fact that the correct value is required for TOTAL for each level in the matrix, you can use IF()+ISINSCOPE() to determine the formula corresponding to the level to which it belongs.

     

    Here are the steps you can follow：

    1. Create measure.

    Measure =
IF(
    ISINSCOPE('Table'[Group3]),[Incorrect total],
IF(
    ISINSCOPE('Table'[Group2]),
    SUMX(VALUES('Table'[Group3]),[Incorrect total]),
IF(
    ISINSCOPE('Table'[Group1]),
    SUMX(ALLEXCEPT('Table','Table'[Group1],'Table'[Group2]),[Incorrect total]),
    SUMX(ALLEXCEPT('Table','Table'[Group1]),[Incorrect total])
    )))

    2. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

4 Replies