2 different dynamic measures in single table with 2 different slicers

[SagarChauhan1]

I need your help with power BI where i want 2 different dynamic measures (Base Sales & Comparision Sales) in single table with 2 different slicer (Actuals version) and Plan version) for each.

[Ashish_Mathur · Accepted solution]

Hi,

Simplify the Sales measure to

Sales = DIVIDE(Switch (SELECTEDVALUE('LC-USD Switch'[Currency]), 
"Local", calculate (sum ('Master Data'[Value (LC)]),'Master Data'[Measure] = "Sales"), "USD", calculate (sum ('Master Data'[Value (USD)]),'Master Data'[Measure] = "Sales")),[Roundings])

Revise the measure to

Base 1 Sales = Calculate ([Sales],REMOVEFILTERS('Version - SF - Comp 1'))

Comp 1 Sales = Calculate ([Sales],REMOVEFILTERS('Version - SF -Base'))

Hope this helps.

[SagarChauhan1]

1st Column answer should be 237,177 (If we select "ACT 2023" in slicer Base"
2nd Column answer should be 252,348 (If we select "AOP 2023" in slicer Comp 1"

[Ashish_Mathur]

I cannot understand why the Data Model is so complex.  Why in the first place are there multiple "Version - SF - Comp 1" tables?  Also, in each of those tables, where are there 3 columns?

[SagarChauhan1]

They all are same, but i thought we needed 2 different slicers hence created "Version - SF -Base" for Base 1 field and  "Version - SF - Comp 1" for Comp 1 field. (Ignore "Version - SF - Comp 2") (You can remove it as required)

[SagarChauhan1]

Version - SF -Base  is taking data from column 1 (From table "Version - SF -Base" and column "Base"
Version - SF - Comp 1 is taking data from column 2 (From table "Version - SF - Comp 1" and column "comp 1"
I created this to have 2 differnet slicers.

[Ashish_Mathur · Accepted solution]

Hi,

Simplify the Sales measure to

Sales = DIVIDE(Switch (SELECTEDVALUE('LC-USD Switch'[Currency]), 
"Local", calculate (sum ('Master Data'[Value (LC)]),'Master Data'[Measure] = "Sales"), "USD", calculate (sum ('Master Data'[Value (USD)]),'Master Data'[Measure] = "Sales")),[Roundings])

Revise the measure to

Base 1 Sales = Calculate ([Sales],REMOVEFILTERS('Version - SF - Comp 1'))

Comp 1 Sales = Calculate ([Sales],REMOVEFILTERS('Version - SF -Base'))

Hope this helps.

[SagarChauhan1 · Accepted solution]

Hi,

It works well for 2 tables but when i add comp 2 measure then it is not working properly. I have used the bellow formula for Comp 2. Pls advice

[SagarChauhan1]

Thank you so much Ashish for your help to resolved this. It is really helpful and appreciate your time.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Use 2 REMOVEFILTERS() in each measure.  DIY.

[SagarChauhan1]

https://drive.google.com/file/d/1cHnOKnUfcFREHgOblzjzEZhs4Yn9e9EZ/view?usp=drive_link

[SagarChauhan1]

Hi, I have shared the file on your email. Unable to add file here.

[Ashish_Mathur]

Upload the file to Google Drive and share the download link here.

[SagarChauhan1]

https://drive.google.com/file/d/1cHnOKnUfcFREHgOblzjzEZhs4Yn9e9EZ/view?usp=drive_link

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result very clearly.