Forum Discussion
Scenarios on FactTable
Dear Helpers,
I hope you can help me. I have a Fact Table like the example below:
| Date | Scenario | Fact |
| 31.12.2021 | 1 | 1.000 |
| 31.12.2021 | 2 | 1.000 |
| 31.01.2022 | 1 | 1.000 |
| 31.01.2022 | 2 | 2.000 |
In the real table there are way more scenarios, Facts and information. I would like to provide my colleagues the opportunity to select two Cases and Compare them with each other. I tried to help me with one slicer on the Scenario Field, and one Slicer that is a distinct copy of the Scenarios and unlinked to the Fact Table. I thought it would be possible to get Scenario 1 with sum(Fact) and Scenario 2 with calculate(sum(Fact),all(Facttable),Filter(Facttable, Scenario = selectedvalue(DistinctScenarioTable))). However, this did not work. Maybe I overthink this? I guess there should be two measures (Scenario 1 Value & Scenario 2 Value). How would be the best way to achieve this?
Best regards
Manuel
Hi Manuel123 ,
So you want two measures based on the selected scenrio in slicer?
measure1 is Scenario1 with sum(Fact).
-- always return sum(Fact) of Scenario1?
measure2 is Scenario2 with calculate(sum(Fact),all(Facttable),Filter(Facttable, Scenario = selectedvalue(DistinctScenarioTable))).
--return sum(Fact) of selected scenario in slicer?
If I understand you correctly, you could refer below formulas.
measure 1 = calculate(sum('table'[fact]),filter(allselected('table'),'table'[scenario] = "scenario1"))
measure 2 = calculate(sum('table'[fact]),filter(allselected('table'),'table'[scenario] = selectedvalue('slcier'[scenario])))
Please correct me if I misunderstood your meaning.Best Regards,
Jay
1 Reply
Hi Manuel123 ,
So you want two measures based on the selected scenrio in slicer?
measure1 is Scenario1 with sum(Fact).
-- always return sum(Fact) of Scenario1?
measure2 is Scenario2 with calculate(sum(Fact),all(Facttable),Filter(Facttable, Scenario = selectedvalue(DistinctScenarioTable))).
--return sum(Fact) of selected scenario in slicer?
If I understand you correctly, you could refer below formulas.
measure 1 = calculate(sum('table'[fact]),filter(allselected('table'),'table'[scenario] = "scenario1"))
measure 2 = calculate(sum('table'[fact]),filter(allselected('table'),'table'[scenario] = selectedvalue('slcier'[scenario])))
Please correct me if I misunderstood your meaning.Best Regards,
Jay