Adding Percent of Total Row to Matrix

There seems like there must be a way, but I'm a bit new to power BI.  I've easily created a pivot/matrix summary table with all the numbers I need except one.....Percent of Total.

Can anyone suggest a way for me to generate this value.  For example, my table looks like the table below.  What do I need to do to add an additional row that calculates the Percent of Total?  So in this example, I'm looking to calculate the values of 40% (40/100) and 60% (60/100).  Is this possible in Power BI?  Help is greatly appreciated.

  1        2 Total

Row 1 20 10 30

Row 2 15 20 35

Row 3 5 30 36

Total 40 60 100

% Total 40% 60%

September 14th, 2015 5:07am

What you want to do is create a Calculated Field/Measure in Power Pivot. The formula will be something like:

PercentTotal1:=SUM([Field1])/SUM([Field3])

PercentTotal2:=SUM([Field2])/SUM([Field3])

Set it to display as %. Add to your matrix.

You may need something like:

PercentTotal:=SUM(ALLSELECTED([Field1]))/SUM(ALL([Field1]))

It is tough to tell from your data.

Free Windows Admin Tool Kit Click here and download it now
September 14th, 2015 10:44am

Greg, thanks for your asssistance.  

My Column headings are using the field ESI_CATEBI[Pass]

My Values are using the field ESI_CATEBI[Tag_ID]

I want the calculation to be  Totals by Pass/Total Records.  Using the concepts you outlined above, the closet I've gotten is using the formula below.  This seems to generate the correct formula for the first first Pass Column, but inexplicably, it shows the same percentage for all the other columns too?  Thought you might be able to identify where I'm going wrong?

% of Total = CALCULATE(COUNT(ESI_CATEBI[Pass]), ALL(ESI_CATEBI))/CALCULATE(SUM(ESI_CATEBI[Pass]), ALL(ESI_CATEBI))

Greg, greatly appreciated. After working with the concepts you presented, I've ended up here, and this is about as close as I've gotten, but still not quite right.

Basically, I want to Divide the totals of the columns (e.g. Column 1 (24399)/Total/109365) 

% of Total = CALCULATE(COUNT(ESI_CATEBI[Pass]), ALL(ESI_CATEBI))/CALCULATE(SUM(ESI_CATEBI[Pass]), ALL(ESI_CATEBI))

This seems to generate the correct percent for the first column, but it repeats the same percentage for all the other columns as well (instead of calculating new ones.  Thought you might be able to help identify the problem? 
Greg, greatly appreciated. After working with the concepts you presented, I've ended up here, and this is about as close as I've gotten, but still not quite right.

Basically, I want to Divide the totals of the columns (e.g. Column 1 (24399)/Total/109365) 

% of Total = CALCULATE(COUNT(ESI_CATEBI[Pass]), ALL(ESI_CATEBI))/CALCULATE(SUM(ESI_CATEBI[Pass]), ALL(ESI_CATEBI))

This seems to generate the correct percent for the first column, but it repeats the same percentage for all the other columns as well (instead of calculating new ones.  Thought you might be able to help identify the problem? 
Greg, greatly appreciated. After working with the concepts you presented, I've ended up here, and this is about as close as I've gotten, but still not quite right.

Basically, I want to Divide the totals of the columns (e.g. Column 1 (24399)/Total/109365) 

% of Total = CALCULATE(COUNT(ESI_CATEBI[Pass]), ALL(ESI_CATEBI))/CALCULATE(SUM(ESI_CATEBI[Pass]), ALL(ESI_CATEBI))

This seems to generate the correct percent for the first column, but it repeats the same percentage for all the other columns as well (instead of calculating new ones.  Thought you might be able to help identify the problem? 
September 14th, 2015 1:36pm

Hi Hawb,

According to your descrption, you need to add rows to your PowerPivot table to show the total and percentage, right?

In PowerPivot data model, we can add calculated column and calculated measure to the tables. However, we can not add rows to table directly. In your scenario, you can use the calculated column to calculate the total, and then use calculated measure to show the percentage. I have tested it on my local environment, the sample DAX expression below is for you reference.
ColumnATotal:=SUM(test0915[ColumnA])
APercentage:=[ColumnATotal]/[AllTotal]

Regards,

Free Windows Admin Tool Kit Click here and download it now
September 15th, 2015 2:30am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics