topic Re: How to create a new measure of % by group in DAX Commands and Tips

How to create a new measure of % by group

Anonymous — Sat, 21 Mar 2020 00:57:43 GMT

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

Re: How to create a new measure of % by group

Greg_Deckler — Sat, 21 Mar 2020 01:46:46 GMT

Sure,

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

PBIX is attached.

Re: How to create a new measure of % by group

Anonymous — Sat, 21 Mar 2020 03:59:57 GMT

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

Re: How to create a new measure of % by group

Anonymous — Sat, 21 Mar 2020 10:25:09 GMT

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

Re: How to create a new measure of % by group

Anonymous — Mon, 30 Mar 2020 17:48:49 GMT

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