Matrix : Drill down column differences for each Category

[w_Das]

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.

[v-shex-msft]

HI @w_Das,

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

[w_Das]

Hi @v-shex-msft 

If Region more than 2, then how do I change dynamicly.

Please advise.

Thanks,

w_Das

[w_Das]

Hi @v-shex-msft 
I want to show the difference with column drill down for each product. Please advise me 

[v-shex-msft]

HI @w_Das,

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

[w_Das]

Hi @v-shex-msft ,

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