cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
quipmaster
Helper II
Helper II

Percent difference for two average scores

Hi Power BI Brain Super-Powered,

 

I could use your thought partnership on this issue.

 

I have average scores for six categories. However, I would like to calculate the percent difference between two average scores at a time.

 

I envision a percent change of  Average Score Dashboard BEFORE vs Average Score Dashboard NOW, Average Score Performance BEFORE vs Average Score Performance NOW, and Average Score Change Management BEFORE vs Average Score Change Management NOW.

 

I figure that I have to use the average score DAX (

Average Progress = AVERAGE('Progress Knowledge'[Progress Rating Scale])

in the % change DAX. But I am not sure about the rest of the % Change DAX. This post seems promising but unsure of how I can adapt DAX.

 

All nudges are greatly appreciated!

 

 

CategoryScore
Dashboard BEFORE.XXXX
Dashboard NOW.XXXX
Progress Monitoring BEFORE.YYYY
Progress Monitoring NOW.YYYY
Change Management BEFORE.ZZZZ
Change Management NOW.ZZZZ

 

 

Progress Knowledge Data Table

quipmaster_0-1665425497189.png

 

 

Progress Knowledge Visual

quipmaster_1-1665425616372.png

 

2 REPLIES 2
amitchandak
Super User
Super User

@quipmaster , In power query use split by a delimiter and use the last delimiter

Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag

 

Then these two will separate out

 

then create a diff measure

 

calculate(Sum(Table[Score]), filter(Table, Table[Category] = "Before")) - calculate(Sum(Table[Score]), filter(Table, Table[Category] = "After")) 

 

or

 

calculate(MAx(Table[Score]), filter(Table, Table[Category] = "Before")) - calculate(MAx(Table[Score]), filter(Table, Table[Category] = "After")) 

Hi @amitchandak,

 

I believe i might missing a step. 

 

Split by Tab did not split the columns. However, split by space almost did the trick.

 

quipmaster_0-1665456175640.png

 

 

I created a custom column to accomplish what I think is the point of the split column.

 

quipmaster_1-1665456228007.png

 

I created this calculation, using your DAX as a guide. In my calculation, I used Now instead of After.

 

Before_Now =
calculate(Sum('Progress Knowledge (2)'[Progress Rating Scale]),
filter('Progress Knowledge (2)','Progress Knowledge (2)'[Before and Now] = "Before")) -
calculate(Sum('Progress Knowledge (2)'[Progress Rating Scale]),
filter('Progress Knowledge (2)', 'Progress Knowledge (2)'[Before and Now] = "Now"))
 
Quick question. The average progress is a  measure.
 
Thank you for getting me closer.
 
My end goal is that when a user hover's over the Progress Monitoring Now bar, a tooltip will appear and say 19.2% increase ((3.7 - 3.1)/ 3.1)) and so on for the other bars.
 
Ave Progress = AVERAGE('Progress Knowledge'[Progress Rating Scale])
 
The DAX would only accept a column. Therefore, I could not use the measure.
 
quipmaster_2-1665456364725.png

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors