cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jojop1972 Member
Member

Counting the results from a measure

Hi

 

I'm new to Power Bi so please excuse me if my question is vague or doesn't make any sense at all, I'm still learning.

 

I am currently writing a report looking at brand purchased by customers.  I have a measure which returns the amount of distinct brands a customer purchases ie 1,2,3,4.  In my matrix I have customer number and the count of brands they have bought.

 The measure is as follows

 

distinctcountbrand = CALCULATE (

DISTINCTCOUNT (Sales[brand]),

CALCULATETABLE (Sales, Sales[Customer No.]))

 

I have verified the results against the main sales table and the results from the measure appear to be OK.

 

 However, my client also wants another matrix displaying how many customers bought 1 distinct brand, 2 distinct brand etc.  I am at a loss where to start.  I have tried filtering on the measure where the value equals 1,2,3,4 etc but the results are wrong. I am literally stabbing in the dark here because of my lack of knowledge.  I would be extremely grateful for any advice for this newbie PowerBi person!

 

Many thanks

 

3 REPLIES 3
v-huizhn-msft Super Contributor
Super Contributor

Re: Counting the results from a measure

Hi @jojop1972,

You can create a calculated column using the formula.

Distinct brand for one customer = CALCULATE(DISTINCTCOUNT(Table[Brand]),ALLEXCEPT(Table,Table[Customer No]))

Than create a slicer including Table[Distinct brand for one customer]. And create a interaction between the slicer and your report including customers.

When you select 1 in slicer, it will display all the customers who bought 1 distinct brand, it will show all customers who bought disticnt 2 brands seperately and so on.

If you have other issues, please feel free to ask.

Best Regards,

Angelia

 

 

jojop1972 Member
Member

Re: Counting the results from a measure

Hi Angelia,

 

Thank you so much for your reply - I have tried your solution and it indeed gives me the distinct brand count for each customer.

However, I have come across another problem and I hope you can help me again!

 

My calculated column is as follows

 

distinct brand column = CALCULATE(DISTINCTCOUNT(Sales[Brand]), ALLEXCEPT(sales, Sales[Customer No.])) as per your advice.  

 

However, I need to introduce a date slicer into my report too in order to be able to see how many customers bought distinct brands over a time period.  When I use a date slicer the results from my distinct brand column do not change and it shows that number of distinct brands bought over the whole time period of the data available in the sales table. I have tried adding datetable[date] to the ALL EXCEPT statement, however this then just lists the distinct brands for each date selected in the date range within the slicer.  I do apologise if the answer to my question is really obvious I am still trying to understand DAX and Power BI and I really appreciate your help.

 

Kind regards

 

Jo.

v-huizhn-msft Super Contributor
Super Contributor

Re: Counting the results from a measure

Hi @jojop1972,

And you'd better list the sample data or screenshot, so we can privode solution which is close to your requirement. It's difficult to reproduce based in your description.

In general, there is only one question in one thread. I suggestion you reopen a thread in this forum. Thanks for understanding.

Best Regards,
Angelia

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 145 members 1,722 guests
Please welcome our newest community members: