Forum Discussion
Create Most Often field
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!!
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 _rateFilter = VAR _max = MAXX(ALLEXCEPT('Table','Table'[Customer]),[Primary Color %]) VAR _result = IF([Primary Color %]=_max,1) RETURN _resultBest Regards,
Gao
Community Support TeamIf 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
7 Replies
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])
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/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?
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 _rateFilter = VAR _max = MAXX(ALLEXCEPT('Table','Table'[Customer]),[Primary Color %]) VAR _result = IF([Primary Color %]=_max,1) RETURN _resultBest Regards,
Gao
Community Support TeamIf 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
