Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Matrix : Drill down column differences for each Category

Hi Team, I have a Matrix table with column drill down, Please advise on the differences for the each category. please share the dax functions for the differences.
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

HI Anonymous,

I'd like to suggest you modify the current columns of matrix to move fiscal year above of the region group. Then these visualization will display the different region values at the same fiscal years group.

formula =
VAR regionA =
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER ( ALLSELECTED ( Table1 ), [Region] = "APAC" ),
        VALUES ( Table1[Product] ),
        VALUES ( Table[Fiscal Year] )
    )
VAR regionB =
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER ( ALLSELECTED ( Table1 ), [Region] = "EMEA" ),
        VALUES ( Table1[Product] ),
        VALUES ( Table[Fiscal Year] )
    )
RETURN
    regionA - regionB

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous 
I want to show the difference with column drill down for each product. Please advise me 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI Anonymous,

    Any other field can be recognized the sort order or index of these field values?

    The text values not able to work as numeric value to extract the current value and loop based on offset to dynamic calculate between different categories.

    Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly  

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

      Thanks for the reply,

       

      I have below scenario,

       

      Single table have 4 region or maybe added in furture (dynamic).
      create Matrix table using 3 layer drilldown 

      Question is : Revenue (value) wants to show matrix table value 
                          Column - Region and difference (if selected two region then difference show selected region varience and drilldown also required)

       

       
      Thanks,

      w_Ds