Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I have a fact table with both actual numbers, and budget numbers like the one below.
Date | Orign | Version | Value |
1/1-2019 | Actual | 1.000,00 | |
1/1-2019 | Budget | A | 900,00 |
1/1-2019 | Budget | A | 150,00 |
1/1-2019 | Budget | B | 1.125,00 |
Beside that table, i've got a disconnected table with the distinct budget versions;
Budget version |
A |
B |
I would now like to create a measure;
SUM([Values])
... but only for the rows where FACT[Version] is equal to the selected/filtered value in 'Budget versions[Version].
Solved! Go to Solution.
Here is one way.
Selected Budget = calculate(sum(FactTable[Value]), treatas(values(Disconnected[Budget Version]), FactTable[Version]))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
The version given by @ibarrau can be expressed in a much more succinct way:
CALCULATE(
SUM('Fact'[Value]),
'Fact'[Version] =
SELECTEDVALUE(Budget[Budget Version])
)
Best
D
Hi. I will assume your real case is different because right now you can just add Version from Fact table to slicer and got the result. Anyway. If there are not so many values you can use Switch like this
Value of target =
SWITCH(
SELECTEDVALUE(Budget[Budget Version];0);
"A"; CALCULATE( SUM('Fact'[Value]) ; 'Fact'[Version] = "A" );
"B"; CALCULATE( SUM('Fact'[Value]) ; 'Fact'[Version] = "B" );
SUM('Fact'[Value])
)
Check it out running:
Hope this helps
Happy to help!
Hey Ibarrau!
Thanks for the shot! I actually came up to your solution aswell, but as i didn't know which entries that will appear in the Version column, i would like to avoid a "hardcoded" solution 🙂
Here is one way.
Selected Budget = calculate(sum(FactTable[Value]), treatas(values(Disconnected[Budget Version]), FactTable[Version]))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hey Pat
That's so awesome - it worked exactly as i hoped for! TREATAS is a new favorite for now 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |