Create Most Often field

[PBINewbie920]

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

[v-cgao-msft · Accepted 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

[tackytechtom]

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/

[PBINewbie920]

@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?

[Greg_Deckler]

@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)

[PBINewbie920]

@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:

[Greg_Deckler]

@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])

[PBINewbie920]

@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: