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.
Hello all, I'm running into what I thought was a simple issue, but its turned into a multiple hours event of my DAX code not working. I'll try to keep it simple in what I'm attempting to do.
I have 2 columns I am attempting to divide, for simplicity, I'll just call them A and B.
I have another date column with year in it. Year is very simple, it has 2021 through 2018.
What I'm trying to do in pseudocode is add up everything from A and B for only 2020-2018 and then divide A by B.
Stuff I have tried:
I'm kinda lost at this point. Normally when I do a CALCULATE its on a single filter, or multiple filters from multiple places, not the same filter with different choices.
Let me know if I can help by giving any additional information. I appreciate any guidence on this, thank you!
Solved! Go to Solution.
Have you tried a measure expression like this? This should work in a card. If you use it in a table/matrix/other visual, you may need to add other terms to the CALCULATE.
A divided by B Prev 3 yrs =
VAR thisyear =
YEAR ( TODAY () )
VAR result =
CALCULATE (
DIVIDE ( SUM ( Table[A] ), SUM ( Table[B] ) ),
Table[Year] >= thisyear - 3
&& Table[Year] <= thisyear - 1
)
RETURN
result
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Have you tried a measure expression like this? This should work in a card. If you use it in a table/matrix/other visual, you may need to add other terms to the CALCULATE.
A divided by B Prev 3 yrs =
VAR thisyear =
YEAR ( TODAY () )
VAR result =
CALCULATE (
DIVIDE ( SUM ( Table[A] ), SUM ( Table[B] ) ),
Table[Year] >= thisyear - 3
&& Table[Year] <= thisyear - 1
)
RETURN
result
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you @mahoneypat
This logic worked well enough to allow me to continue to troubleshoot further. I am indeed using this in a table. The result returns slightly skrewed numbers than I would expect, but this isn't a problem with the DAX logic.
So close, yet so far!
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
148 | |
116 | |
104 | |
89 | |
65 |