Forum Discussion
Calculating DISTINTCOUNT within Table
Hi all,
I have been struggling with a measure/ or logic that I hope some of you can help me with. I have a snapshot of my data set below:
| Product Key | Customer key | Date Key | Line item | Value | Price point | |
| 100 | 333 | 30. juni 2021 | Units | 2 | Low | |
| 110 | 333 | 30. juni 2021 | Units | 3 | Low | |
| 121 | 333 | 30. juni 2021 | Units | 0 | ||
| 133 | 444 | 30. juni 2021 | Units | 0 | Medium | |
| 146 | 444 | 30. juni 2021 | Units | 0 | High | |
| 100 | 555 | 30. juni 2021 | Units | 0 | Low | |
| 161 | 333 | 29. juni 2021 | Units | 2 | High | |
| 177 | 555 | 29. juni 2021 | Units | 2 | Low | |
| 195 | 555 | 29. juni 2021 | Units | 2 | Low | |
| 110 | 333 | 29. juni 2021 | Units | 2 | Low | |
| 100 | 333 | 29. juni 2021 | Units | 2 | Best |
I'm trying to figure out how many unique customers have purchased either 0,1,2,3, or 4 of the price points. So, for example, if customer 166 purchased a low and best product, he should get a 2
So I have tried to create a measure that calculates unique local customers based on their unique price point. An example of the measure logic is below:
# of 1 PP POS =
CALCULATE (
DISTINCTCOUNT ( Financials[Local Customer Key] ),
Financials[Value] > 0,
FILTER (
Financials,
CALCULATE (
DISTINCTCOUNT ( Financials[Price Point] ),
Financials[Value]>0,
Financials[Price Point] <> BLANK ()
) = 1
)
)
However, this does not return what is desired, which I presume is due to the difficulties in having a measure working with an if statement (Distinctount = 1)
It's my hope in the end to get a visual like the one below
Any ideas or advice is well received.
I really appreciate any help you can provide.
Anonymous , You need to create a measure then you need to create a bucket and use that
measure =
CALCULATE (
DISTINCTCOUNT ( Financials[Price Point] ),
Financials[Value]>0,
Financials[Price Point] <> BLANK ()
)
This value you need to join with an independent table having value 0,1,2,3,4 (using generate series)
A final measure like
Similar example for details
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-ksumx(filter(Values(Table[Customer]), [Measure] = max(Bucket[Value])),[Measure])
3 Replies
- amitchandak
Super User
Anonymous , You need to create a measure then you need to create a bucket and use that
measure =
CALCULATE (
DISTINCTCOUNT ( Financials[Price Point] ),
Financials[Value]>0,
Financials[Price Point] <> BLANK ()
)
This value you need to join with an independent table having value 0,1,2,3,4 (using generate series)
A final measure like
Similar example for details
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-ksumx(filter(Values(Table[Customer]), [Measure] = max(Bucket[Value])),[Measure])
Hi again Amithandak,
I considering writing a new post for a follow up issue, but I thought it might be better to try it here since most of the information is here. So the solution with the buckets did work out very well. However, one thing I have noticed is that cross filtering doesnt work as hoped between my visuals. I can pick a customer and it will filer on the price points, but I cannot filter on the number of price point visuals and get the list of customers that has that number of price points. I believe this is due to no relationship exists between the facttable and the "bucket" table. Are you aware of a way to create a relationship or another workaround to make cross filtering possible? Thank you much in advance.