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,
I have a running total measure for a value for all the months in 2022 that is a combination of the actual values (for jan. to may) and it’s estimated value for future months, and I want to color code the months so that anything present/past is shown in one color (“current”) and anything in the future is a different color (“LE”). For color coding, I created a formula and added it as Legend to show a 2 color distinction between the 2 options. However by doing this, the running total measure in the columns chart just computes Jan to May and then it restarts after May with a running total computed from June to Dec (but only if I leave the color coding formula on Legends. Once it’s removed, the running total works fine). Is there any way to fix the color coding for the running total so that regardless if its labelled as “current” or “LE”, the graph keeps showing the running total for all the months in 2022?
Here are my formulas:
Running Total =
CALCULATE(
SUM('Table'[Value]),
FILTER(
ALLSELECTED('Table'[Period]),
ISONORAFTER('Table'[Period], MAX('Table'[Period]), DESC)
)
)
Current vs LE =
IF('Table'[Period]<=TODAY(),"Current", "LE")
Any help is much appreciated!
Solved! Go to Solution.
Hi, @tomekm ,
Measure can't be used as a legend, only column can be used as a legend, so you can share a simple file and want the output to be displayed, maybe using conditional format.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @tomekm ,
Measure can't be used as a legend, only column can be used as a legend, so you can share a simple file and want the output to be displayed, maybe using conditional format.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@tomekm , Try like
Running Total =
CALCULATE(
SUM('Table'[Value]),
FILTER(
ALL('Table'[Period]),
ISONORAFTER('Table'[Period], MAX('Table'[Period]), DESC)
)
)
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Can you share issue screenshot and expected value
Thanks. I tried your formula but it didn't work for me.
But here's the basic sample data that I'm working with, and below are 2 screenshots:
Period | Value |
2022-01 | 1200 |
2022-02 | 390 |
2022-03 | 570 |
2022-04 | 443 |
2022-05 | 831 |
2022-06 | 590 |
The desired output is the one below. Here I manually adjusted the color for the first 5 periods. But I would like to be able to have this automatically adjusted via a formula, placed on Legend.
The screenshot below is the wrong output that I currently get when I place my "Current vs LE" formula on Legends:
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |