Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Employee
Employee

Hi @Anonymous,

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

 

 

Anonymous
Not applicable

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.

Hi @Anonymous,

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.