Forum Discussion

Zaynah16's avatar
Zaynah16
Helper I
4 years ago
Solved

Measure for Percentage difference

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

  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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

6 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    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.

  • Zaynah16's avatar
    Zaynah16
    Helper I
    4 years ago

    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 

     

     

    PILLARBusiness UnitSectionSub SectionItemStatusScore
         On track1
         On track1
         On track1
         Lagging2
         Lagging2
         Lagging2
         Lagging2
         No data0
         No data0
         No data0
         No data0
         At risk3
         At risk3
         At risk3
         At risk3
         At risk3
         At risk3
    • SpartaBI's avatar
      SpartaBI
      Community Champion
      4 years ago

      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