How to do the calculation % of counts from a table ?

[asas27]

Hi Sir/Madam,

I had a table in the following format.

Product	Shop	Delivery Country
A	1	US
A	1	US
A	2	Canada
A	2	US
B	2	US
B	3	US
B	1	Canada
B	3	Canada
B	2	Canada

Then I need to prepare another table to show the following data:

Product	Percentage from Shop 1	Percentage to US	Percentage from Shop 1 to US
A	How?	How?	How?
B	How?	How?	How?

Would you mind to teach me how to calculate those % in second table?  Many thanks!

Best regards,

Catherine

[V-lianl-msft · Accepted solution]

Hi @asas27 ,

First, create a product table

Table 2 = DISTINCT('Table'[Product])

Create a one to many relationship between the product table and the original table.

Try to create measures like this:

Percentage from Shop 1 =
VAR count_shop1 =
    CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Shop] = 1 ) )
VAR count_all =
    COUNTROWS ( 'Table' )
RETURN
    DIVIDE ( count_shop1, count_all )

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dedelman_clng]

Hi @asas27 -

You don't give the answers you are specifically looking for, but here's what I think you want:

New Measure:

TotalSalesPct = CALCULATE(COUNTROWS('Product')) / CALCULATE(COUNTROWS(ALL('Product')))

3 matrix visuals

Hope this helps

David