How to create a new measure of % by group

[laurelning]

Hi all,

    I am new to DAX and I believe this is a very simple question to the experts here. I have a survey question that asks customer to respond why they frequent a certain store and they can check multiple reasons. I have two data sets, one having the customer IDs and the reason; the other data file having again customer IDs and the stores they visited.  If, for example, we have a total of 10 customers, among them, 8 check location... how to get the percentage based on a total of 10 customers? Please see below the excel table that I would like to create instead in power bi.

The sample data is here to download :

https://drive.google.com/open?id=19NYmpV0ulE_Hhla0pCZFFcpFB56mXmI7

Thanks in advance and your help is highly appreciated!

Laurel

[Greg_Deckler]

Sure,

Measure = DIVIDE(COUNTROWS('Sheet1'),COUNTROWS(DISTINCT(ALL(Sheet2[Customer ID]))),0)

PBIX is attached. 

[laurelning]

@Greg_Deckler 

Thanks for helping out. I look forward to learning from your book. Maybe I haven't explained clear enough in the first post. The exact excel table I look for in power bi is :

And the measure created gives me the following:

I hope to have the percentage of reasons by stores. 

Thanks.

Laurel

[Anonymous · Accepted solution]

Here's the full and correct solution. The answer does not lie in DAX. It lies in the data model.

https://1drv.ms/u/s!ApyQEauTSLtOgYM53H8bs_VEwonrUA?e=1mhVYz

Best

D

[laurelning]

@Anonymous  Thanks! It works and serves the purpose. I was ablto to follow your example and replicate it to my very complex data. There is still a place that doesn't come back to be right though. Also I am wondering if there is way that we can make it work simpler. Will come back to you with more questions. Thanks again for your time!

Great to have the community here to support!

Ling