locked
Adding Percent of Total Row to Matrix RRS feed

  • Question

  • 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%

    Monday, September 14, 2015 9:06 AM

Answers

  • 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,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Tuesday, September 22, 2015 8:51 AM
    • Marked as answer by Charlie Liao Thursday, October 8, 2015 9:27 AM
    Tuesday, September 15, 2015 6:29 AM

All replies

  • 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.

    Monday, September 14, 2015 2:43 PM
  • 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? 
    Monday, September 14, 2015 5:35 PM
  • 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,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Tuesday, September 22, 2015 8:51 AM
    • Marked as answer by Charlie Liao Thursday, October 8, 2015 9:27 AM
    Tuesday, September 15, 2015 6:29 AM
  • Hi Hawb,

    Based on your initial post, it appears that the sample data provided so far is based on how the matrix looks. If this is the case, can you kindly provide more details about the underlying tables in your model that are used to produce the current version of your matrix. Table names, column names, a brief description of the participating relationships, and an example of how the actual data looks would be very helpful. In addition to this, letting us know what columns you are placing in the row and column sections of your matrix will also be quite handy.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Wednesday, September 16, 2015 8:47 PM