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 data set I am trying to summarize using a measure in a matrix visual. The data is looking at whether certain trains running on time or not, with the data set being records of various trips. Summarizing the count of trips looks like this:
On Time | Late | |
Train A | 90 | 10 |
Train B | 75 | |
Train C | 50 | 50 |
When I then make use of my measure to summarize the percentage trips that are late, I get this:
Train A | 10% |
Train C | 50% |
What I really want though is for there to also be a 'Train B' row with 0% in there. I assume it's because there are no trips that were late and so I'm getting a blank, but various efforts to make it appear (checking for a blank value, adding a 0 to the measure) aren't doing much for me. The DAX looks like this:
% Trips Late =
CALCULATE(
DISTINCTCOUNT('TRAIN_TRIPS'[TRIP_ID]),
'TRAIN_TRIPS'[LATE_FLAG] = "Late"
)
/
CALCULATE(
DISTINCTCOUNT('TRAIN_TRIPS'[TRIP_ID]),
'TRAIN_TRIPS'[LATE_FLAG] IN {"Late", "On Time"}
)
What do I need to do differently to get the table I need?
Thanks for your help!
Solved! Go to Solution.
Hi @pbiuser12345 ,
This Solves your problem (need to use DIVIDE function):
% Trips Late =
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( 'TRAIN_TRIPS'[TRIP_ID] ),
'TRAIN_TRIPS'[LATE_FLAG] = "Late"
),
CALCULATE (
DISTINCTCOUNT ( 'TRAIN_TRIPS'[TRIP_ID] ),
'TRAIN_TRIPS'[LATE_FLAG] IN { "Late", "On Time" }
)
) + 0
If this solves your question, Please give it a thumbs up and accept it as a solution to make it easier for the others to find what they are looking for.
Regards,
Loran
Hi @pbiuser12345 ,
This Solves your problem (need to use DIVIDE function):
% Trips Late =
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( 'TRAIN_TRIPS'[TRIP_ID] ),
'TRAIN_TRIPS'[LATE_FLAG] = "Late"
),
CALCULATE (
DISTINCTCOUNT ( 'TRAIN_TRIPS'[TRIP_ID] ),
'TRAIN_TRIPS'[LATE_FLAG] IN { "Late", "On Time" }
)
) + 0
If this solves your question, Please give it a thumbs up and accept it as a solution to make it easier for the others to find what they are looking for.
Regards,
Loran
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 |
---|---|
44 | |
21 | |
20 | |
16 | |
15 |
User | Count |
---|---|
46 | |
28 | |
25 | |
18 | |
17 |