Get percentage by Column Total in Matrix

[abbynie08]

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!

[v-juanli-msft · Accepted solution]

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

[v-juanli-msft]

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.

[Ashish_Mathur]

Hi,

Does this measure work?

Perent of Total = DIVIDE(DISTINCTCOUNT('Table'[Orders]),CALCULATE(DISTINCTCOUNT('Table'[Orders]),ALL('Table','Table'[Fruit])))

[abbynie08]

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]

Hi,

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

[jdbuchanan71]

@abbynie08 

Try your measure with a KEEPFILTERS added.

Perent of Total =
DIVIDE (
    DISTINCTCOUNT ( 'Table'[Orders] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Orders] ),
        KEEPFILTERS ( ALLEXCEPT ( 'Table', [Country] ) )
    )
)

[abbynie08]

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