Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

 

 

Progress Knowledge Visual

 

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , 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")) 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

       

       

       

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

       

       

      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.