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 all,
What I am trying to do should be very simple. I have a Table holding an ID, NAME, DATE, VALUE and STATUS.
The table could look something like this:
- ID & NAME will always be the same in pairs.
- DATE can be different for every pair.
- VALUE should be summed together for each ID & NAME pair.
- Status should be taken from a row that doesn't have value 99 (it's a palceholder in my program)
In the end the table should look like this:
I have colored the old and new row(s) to see how they transform.
Is what I want to achieve possible?
Thank you for the help.
Solved! Go to Solution.
Hi @Anonymous
1. Place ID, Date and Name in a table visual
2. Place these measures in the visual
ValueM =
SUM(Table1[Value])
StatusM =
CALCULATE ( DISTINCT ( Table1[Status] ), Table1[Status] <> 99 )
Note [StatusM] assumes there's only 1 Status value different from 99 per ID-Name-Date
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Anonymous
1. Place ID, Date and Name in a table visual
2. Place these measures in the visual
ValueM =
SUM(Table1[Value])
StatusM =
CALCULATE ( DISTINCT ( Table1[Status] ), Table1[Status] <> 99 )
Note [StatusM] assumes there's only 1 Status value different from 99 per ID-Name-Date
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hey @AlB,
This works absolutely perfect!
The StatusM didn't quite work for me (as you said, more than 1 Status different from 99 would not work, which I have). But I still got it to work; I just put the Status itself in the table and because it's always the first row that holds the Status that works.
So really all I needed was ValueM.
Thank you very much for the help 🙂
Edit: I have no idea how it worked when I wrote this comment, but since then the Status doesn't work anymore. It's just the sum of all together, which is obviously wrong.
I tried to put the Measure in a MIN() or FIRSTNONBLANK() to just select one value, but with no success.
Edit2: I got it working again. I simply had to unselect "Sum" from Status in the Visualization window in the report. With this I also got the sum of VALUE without having to create a measure.
For any future visitors: Select Table -> Visualizations Tab -> Right Click your Column under Values -> Click "Sum". And for the Status select "Don't summarize" in the last step.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |