topic Re: Measure for Percentage difference in DAX Commands and Tips

Measure for Percentage difference

Zaynah16 — Wed, 22 Jun 2022 12:30:55 GMT

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

Re: Measure for Percentage difference

SpartaBI — Wed, 22 Jun 2022 12:34:58 GMT

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.

Re: Measure for Percentage difference

Zaynah16 — Wed, 22 Jun 2022 13:00:36 GMT

SpartaBI

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

Re: Measure for Percentage difference

SpartaBI — Wed, 22 Jun 2022 13:09:59 GMT

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

Showcase Report – Contoso By SpartaBI

Re: Measure for Percentage difference

Zaynah16 — Wed, 22 Jun 2022 14:53:04 GMT

Thank you! This worked!

Re: Measure for Percentage difference

SpartaBI — Wed, 22 Jun 2022 14:54:25 GMT

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 🙂

Re: Measure for Percentage difference

Zaynah16 — Thu, 23 Jun 2022 18:22:33 GMT

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