Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi everyone
Please could someone help me with a measure
I need to calculate the percentage difference between two status's ( on track = 1 and at risk = 2)
Calculation
Total number % number of green(1) = percentage of green
Total number % total number of red (2) = percentage of red
percentage of green minus percentage of red = total score
Data Stucture
Tatble name : RAG status
Column 1 : Item
Column 2 :Status
Column 3: Score
Thanks in advance
Z
Solved! Go to Solution.
@Zaynah16 I'm not sure what do you mean by 'percentage total score' 🙂
Anyway I took a guess, maybe this will anyway show you the way:
_Measure =
VAR _pct_on_track =
DIVIDE(
CALCULATE(
SUM(RAG status[Score]),
RAG status[Status] = "On Track"
),
CALCULATE(
SUM(RAG status[Score]),
REMOVEFILTERS(RAG status[Status])
)
)
VAR _pct_at_risk =
DIVIDE(
CALCULATE(
SUM(RAG status[Score]),
RAG status[Status] = "At Risk"
),
CALCULATE(
SUM(RAG status[Score]),
REMOVEFILTERS(RAG status[Status])
)
)
RETURN
_pct_on_track - _pct_at_risk
Sample data - i need a dax calc/ measure to calculate the percentage score.
eg.
289 items
79 are on track
60 are at risk
I need a percentage total score of the on track minus at risk
PILLAR | Business Unit | Section | Sub Section | Item | Status | Score |
On track | 1 | |||||
On track | 1 | |||||
On track | 1 | |||||
Lagging | 2 | |||||
Lagging | 2 | |||||
Lagging | 2 | |||||
Lagging | 2 | |||||
No data | 0 | |||||
No data | 0 | |||||
No data | 0 | |||||
No data | 0 | |||||
At risk | 3 | |||||
At risk | 3 | |||||
At risk | 3 | |||||
At risk | 3 | |||||
At risk | 3 | |||||
At risk | 3 |
@Zaynah16 I'm not sure what do you mean by 'percentage total score' 🙂
Anyway I took a guess, maybe this will anyway show you the way:
_Measure =
VAR _pct_on_track =
DIVIDE(
CALCULATE(
SUM(RAG status[Score]),
RAG status[Status] = "On Track"
),
CALCULATE(
SUM(RAG status[Score]),
REMOVEFILTERS(RAG status[Status])
)
)
VAR _pct_at_risk =
DIVIDE(
CALCULATE(
SUM(RAG status[Score]),
RAG status[Status] = "At Risk"
),
CALCULATE(
SUM(RAG status[Score]),
REMOVEFILTERS(RAG status[Status])
)
)
RETURN
_pct_on_track - _pct_at_risk
Thank you! This worked!
@Zaynah16 my pleasure 🙂 Will appreciate your Kudos 🙂
P.S. check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂
Hi @SpartaBI
Another quiestion please
I've got a graph with the above data however when i click on the No data or lagging on the grapgh it gives me a percentage. How do i exclude lagging and no data and only have a result when clicking on the on track or at risk. if possible could we have it return N/A when clicking on the no data and lagging
@Zaynah16 not sure what you need exactly. Can you supply sample data (copy paste of few rows) and the desired result hard coded after + the logic for that result.
User | Count |
---|---|
66 | |
46 | |
20 | |
19 | |
15 |
User | Count |
---|---|
121 | |
41 | |
38 | |
28 | |
23 |