Forum Discussion

abbynie08's avatar
abbynie08
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Get percentage by Column Total in Matrix

Hi all, 

I have some trouble in getting the percentage value of the column total. Below is my data

The top table shows the number of orders for a specific fruit in a spefic country. The measure created is distinctcount(orders)

What I want to do here, is to get the table below, which shows the percentage by column.

 

1st method: I can do this by using shown value as column total in the PBI, however, the percentage shows has two decimals, which I want 0 decimals. If there is a way to change the decimals, I can use this method. Does anyone know how to do this?

 

2. By creating a measure myself

 

Perent of Total =
DIVIDE (
DISTINCTCOUNT('Table'[Orders]),
CALCULATE ( DISTINCTCOUNT('Table'[Orders] ), ALLEXCEPT ('Table',[Country]) )
)
 
However, I have a lot of other filters from different tables that I want to be applied to this chart. If I create this measure, the data will not change if I select those filters, which doesnt work in my case.
 
Can someone help me with this please? I have been exploring for 3 hours. Thanks!
 
 
Need Help
Tips and Tricks

7 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    6 years ago

    abbynie08 

    Try your measure with a KEEPFILTERS added.

    Perent of Total =
DIVIDE (
    DISTINCTCOUNT ( 'Table'[Orders] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Orders] ),
        KEEPFILTERS ( ALLEXCEPT ( 'Table', [Country] ) )
    )
)
    • abbynie08's avatar
      abbynie08
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      Thank you for replying. Unfortunately, this does not work. The percentages in the matrix are all 100%. I tried to create a measure of half of your code, which list below to see why that happens.

       

      Column Total =
CALCULATE (
        DISTINCTCOUNT ( 'Table'[Orders] ),
        KEEPFILTERS ( ALLEXCEPT ( 'Table', [Country] ) )
    )

       

       It seems that the value returned are just as the first table that I put in my post, which is the same as distinctcount('Table' [Orders]). Can you help me with this? Thanks

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    Does this measure work?

    Perent of Total = DIVIDE(DISTINCTCOUNT('Table'[Orders]),CALCULATE(DISTINCTCOUNT('Table'[Orders]),ALL('Table','Table'[Fruit])))
    • abbynie08's avatar
      abbynie08
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      Thanks for replying. When I put in your code, it shows an eror, "Multiple table arguments are not allowed in the ALL/ALLNOBLANKROW function." I think what we can do is to put only the column name in all function.

       

      However, there is a question. When I put in "Perent of Total = DIVIDE(DISTINCTCOUNT('Table'[Orders]),CALCULATE(DISTINCTCOUNT('Table'[Orders]),ALL('Table','Table'[Fruit])))". It shows 100%. But, the intersting thing is when I put in "Perent of Total Row = DIVIDE(DISTINCTCOUNT('Table'[Orders]),CALCULATE(DISTINCTCOUNT('Table'[Orders]),ALL('Table','Table'[Country]))). It shows the percentage of each row total. 

      So the tricky part is why does this funciton works on the row, not on total?

       

      Thanks

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

        Hi,

        Share the link from where i can download your PBI file and show the expected result there very clearly.

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi abbynie08 

    Create a measure

    Measure = DISTINCTCOUNT('Table'[id])/CALCULATE(DISTINCTCOUNT('Table'[id]),ALL('Table'[cate]))

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi abbynie08 

    According to summary by  Greg_Deckler : 

    This is almost certainly caused by what is referred to as “the measure totals problem”. This one is extremely common. See this post that explains it, Dealing with Measure Totals

    Also, this Quick Measure, Measure Totals, The Final Word should get you what you need.

    Matrix Measure Total Triple Threat Rock & Roll (MM3TR&R) can also be useful.

    Or this Quick Measure submission, Table Matrix Totals or Subtotals.

     

    If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

    If not, please feel free to let me know.

     

    Best Regards

    Maggie