Percentage total of measure

[imranamikhan]

Hello,

Context:

I have a data source which has multiple questions and scores. Each question is grouped to a "sub theme" and a "category". I have created a matrix table and displayed the sub theme.

In this table, I have created the below measure to sum the score in the data source.

Total score by sub theme =

SUM ( REF_DIM_DB_Benefits_Unpivot[Score] )

I have created another measure which counts number of categories associated to each theme.

Sub Theme Category Count = CALCULATE(

DISTINCTCOUNT(REF_DIM_DB_Benefits_Unpivot[Mapping_table.Category]),

ALLEXCEPT(REF_DIM_DB_Benefits_Unpivot,REF_DIM_DB_Benefits_Unpivot[Mapping_table.Sub Theme])

)

I have then created a final measure which divides the total score by the number of categories:

REF Total score by sub theme / category count =

ROUNDUP(SUMX(VALUES(REF_DIM_DB_Benefits_Unpivot[Mapping_table.Category]),

[Total score by category / question count])/[Sub Theme Category Count],0)

Problem:

I want to create another column which displays the percentage of the grand total of this measure and to allow the table to be filtered by a slicer (project title). I have spent a couple of hours going through the forums but unfortunately I am not yet equipped with the knowledge to identify a working solution.

Any support would be appreciated.

Best regards,

AmiKhan

What the data source looks like:

[AllisonKennedy]

@imranamikhan  can you please add a column of your desired results? 

I think you might find AVERAGEX useful here? 

AverageValue = AVERAGEX ( VALUES (REF_DIM_DB_Benefits_Unpivot[Mapping_table.Category]), REF_DIM_DB_Benefits_Unpivot[Score] )

You can then use the down arrow on values to get % of GT values, or use the ALLSELECTED function to calculate this as well. 

[imranamikhan]

Thanks Allison but I need % of grand total values from the measure outputs of "Total score by sub theme / category count". Example in Excel below:

[AllisonKennedy · Accepted solution]

@imranamikhan  you should be able to do this using the built in % of GT option: 

Otherwise, do something like:

% GT Measure = DIVIDE( [Measure], CALCULATE ( [Measure] , ALLSELECTED( table[category]) ) )

[imranamikhan]

Thanks @AllisonKennedy.

I re-read @Greg_Deckler  post on this subject and I realised I was not following his advice correctly.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/td-p/547907

Final working measures

01. Sub Theme Count = CALCULATE(
   DISTINCTCOUNT(REF_DIM_DB_Benefits_Unpivot[Mapping_table.Category]),
   ALLEXCEPT(REF_DIM_DB_Benefits_Unpivot,REF_DIM_DB_Benefits_Unpivot[Mapping_table.Sub Theme])
   )
    //count number of themes

02. Divide score by Sub Theme Count = DIVIDE(
    SUM('REF_DIM_DB_Benefits_Unpivot'[Score]),
    [01. Sub Theme Count])

03. Divide score by Category count = 
ROUNDUP(SUM ( REF_DIM_DB_Benefits_Unpivot[Score] ) / COUNT( ( REF_DIM_DB_Benefits_Unpivot[Mapping_table.Category] )),0)
//divide by score by a count of categories

04. Measure_Total = 
VAR __table = SUMMARIZE(REF_DIM_DB_Benefits_Unpivot,[Mapping_table.Sub Theme],"__value",[03. Divide score by Category count])
RETURN
IF(HASONEVALUE(REF_DIM_DB_Benefits_Unpivot[Mapping_table.Sub Theme]),[03. Divide score by Category count],SUMX(__table,[__value]))

best regards,

AmiKhan