Total sales based on slicer selections

[Anonymous]

Helo experts,

I am trying to achieve a requirement using below dataset (which can't be altered). Hope i will get some solutions to achieve this.

Year	FY19_Sales	FY20_Sales	FY21_Sales	FY22_Sales
FY19	13	874	4565	874
FY19	343	372	22323	372
FY19	74	22	131231	22
FY20	874	13	23	456
FY20	372	343	21	223
FY20	22	74	121	131231
FY21	4565	23	13	23
FY21	22323	21	343	21
FY21	13123	121	74	121
FY22	23	4565	874	13
FY22	21	22323	372	343
FY22	121	31231	22	74
Total	41874	59982	159982	133773

I have to display a slicer with values FY19, FY20, FY21... Based on slicer selection, respective KPI value should be displayed.

Ex: If i select FY19 in slicer, KPI should display Sum(FY19_Sales) which is 41874. Based on slicer selection the KPI value should dynamically change. Can anyone please help me to achieve this using DAX.

Note: Totals in sample data are added for reference, which are not part of actual dataset.

Regards,

Chunduri

[PaulOlding]

Hi @Anonymous 

Here's a measure that returns the sum based on a single value selected in a slicer.

Sales Amt. = 
SWITCH(
    SELECTEDVALUE('Table'[Year]),
    "FY19", CALCULATE(SUM('Table'[FY19_Sales]), REMOVEFILTERS('Table'[Year])),
    "FY20", CALCULATE(SUM('Table'[FY20_Sales]), REMOVEFILTERS('Table'[Year])),
    "FY21", CALCULATE(SUM('Table'[FY21_Sales]), REMOVEFILTERS('Table'[Year])),
    "FY22", CALCULATE(SUM('Table'[FY22_Sales]), REMOVEFILTERS('Table'[Year]))
    )

It's a shame you cannot remodel this data.  The DAX could easily get more and more difficult because of the way it's currently shaped.