Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
My data curretly looks like this:
Customer | Color |
Customer A | Red |
Customer A | Red |
Customer A | Red |
Customer A | Red |
Customer A | Orange |
Customer A | Green |
Customer A | Blue |
Customer A | Pink |
Customer A | Pink |
Customer A | Purple |
Customer B | Red |
Customer B | Orange |
Customer B | Yellow |
Customer B | Yellow |
Customer B | Yellow |
My goal is to summarize it in PBI to look like this:
Customer | Primary Color | Primary Color % |
Customer A | Red | 40% |
Customer B | Yellow | 60% |
Any thoughts how I can do this? I cant figure out a way to get the most often and the percent related to it,
Any input would be appreciated!!
Solved! Go to Solution.
Hi @PBINewbie920 ,
Please try these measures:
Primary Color % =
VAR _customer = MAX('Table'[Customer])
VAR _color = MAX('Table'[Color])
VAR _count =
CALCULATE (
COUNT ( 'Table'[Customer] ),
FILTER (
ALL ( 'Table' ),
'Table'[Customer] = _customer
&& 'Table'[Color] = _color
)
)
VAR _count_all =
CALCULATE (
COUNT ( 'Table'[Customer] ),
FILTER ( ALL ( 'Table' ), 'Table'[Customer] = _customer )
)
VAR _rate =
DIVIDE ( _count, _count_all )
RETURN
_rate
Filter =
VAR _max = MAXX(ALLEXCEPT('Table','Table'[Customer]),[Primary Color %])
VAR _result = IF([Primary Color %]=_max,1)
RETURN
_result
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Hi @PBINewbie920 ,
Please try these measures:
Primary Color % =
VAR _customer = MAX('Table'[Customer])
VAR _color = MAX('Table'[Color])
VAR _count =
CALCULATE (
COUNT ( 'Table'[Customer] ),
FILTER (
ALL ( 'Table' ),
'Table'[Customer] = _customer
&& 'Table'[Color] = _color
)
)
VAR _count_all =
CALCULATE (
COUNT ( 'Table'[Customer] ),
FILTER ( ALL ( 'Table' ), 'Table'[Customer] = _customer )
)
VAR _rate =
DIVIDE ( _count, _count_all )
RETURN
_rate
Filter =
VAR _max = MAXX(ALLEXCEPT('Table','Table'[Customer]),[Primary Color %])
VAR _result = IF([Primary Color %]=_max,1)
RETURN
_result
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Hi @PBINewbie920 ,
Here a possible solution:
Here my three measures:
CountMeasure = COUNTROWS ( 'Table' )
TopCountMeasure = CONCATENATEX ( TOPN ( 1, VALUES ( 'Table'[Color] ), [CountMeasure] ), 'Table'[Color], ", " )
PercentageMeasure = DIVIDE ( MAXX ( VALUES ( 'Table'[Color] ), [CountMeasure] ), [CountMeasure] )
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
@tackytechtom Thank you! I gave this a try and it looks like something is off, its not giving me the top result but just showing me all the options?
@PBINewbie920 Maybe:
Primary Color Measure =
VAR __Table = SUMMARIZE('Table',[Customer],[Color],"__Count",COUNTROWS('Table')
VAR __Max = MAXX(__Table,[__Count])
RETURN
MAXX(FILTER(__Table,[__Count]=__Max),[Color])
and you can also do this:
Primary Color % =
VAR __Total = COUNTROWS('Table')
VAR __Color = [Primary Color Measure]
VAR __Count = COUNTROWS(FILTER('Table',[Color] = __Color)
RETURN
DIVIDE(__Count, __Total, 0)
@Greg_Deckler Hi! thank you!! I tried to use this in DAX as a column, and as a created measure, but both ways I keep getting an error:
@PBINewbie920 Missed a closing paren:
Primary Color Measure =
VAR __Table = SUMMARIZE('Table',[Customer],[Color],"__Count",COUNTROWS('Table'))
VAR __Max = MAXX(__Table,[__Count])
RETURN
MAXX(FILTER(__Table,[__Count]=__Max),[Color])
@Greg_Deckler Thanks! Looks like its not able to distinguish between the different Customers-- like customer B should be yellow. However it looks like its just picking the top from color in general:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |