Count Based on Group

[Anonymous]

Dear All,

     I have a data like this,

Sample File is attached for your ref:  Sample PBIX

In this what I have already calculated is --> "The Maximum Sales". Referring to the above image, Maximum of Sales column is 7 out of 11 which is the total sales count. So, out of 11, i have maximum sales = 7. Refer the below measure used,

Maximum Sales = 
CONCATENATE (
    "Maximum Sales ",
    "(" & MAXX ( VALUES ( 'Table'[Sales] ), CALCULATE ( COUNT ( 'Table'[Sales] ) ) ) & " / "
        & COUNTROWS ( 'Table' ) & ")"
)

Now, What i wanted is, to get the each value of Reasons contributing to the Maximum Sales in a Card Visual. How can i achieve this in an easy way? Is there any other alternative way to achieve this in an wasy manner. Or, split this formula into separate parts I mean, the different measures for my easy understanding. Kindly request you to elaborate if possible that would be really helpful to me to understand what is going on each steps. Please help me. This is urgent.

Measure = 
CONCATENATEX (
    TOPN (
        1,
        SUMMARIZE (
            'Table',
            [Sales],
            'Table'[Reason],
            "Max Sales", COUNT ( 'Table'[Sales] )
        ),
        CALCULATE (
            MAXX ( VALUES ( 'Table'[Sales] ), CALCULATE ( COUNT ( 'Table'[Sales] ) ) ),
            ALLEXCEPT ( 'Table', 'Table'[Sales] )
        ), DESC
    ),
    [Max Sales] & " - " & [Reason],
    UNICHAR ( 10 ),
    [Max Sales], DESC
)

Thanks & Regards,

Praveen.

[yelsherif · Accepted solution]

Here is a measure to solve your problem

Split =

var summaryTable = summarize(Table1,Table1[Sales],"count", count(Table1[ID]))

var salesName = FIRSTNONBLANK(SELECTCOLUMNS(filter(summaryTable,maxx(summaryTable,[count])),"Sales",[Sales]),true)

var theSplit = filter(summarize(Table1,Table1[Sales],Table1[Reason],"count", count(Table1[ID])),Table1[Sales]=salesName)

return

concatenatex(theSplit, [count] & " " & [Reason], ", ")

[yelsherif]

I hope the measure worked for you, please accept it as a solution

Thanks