Winning Percentage by grouping in DAX

[Anonymous]

Hi, 

I have the following data with me:

And I want to basically show each controller with status having max lives and percent as below:

Controller	Winning Percent
Zing Health	Unknown (45.60%)
YamHill County Care organization	Unknown (50.33%)
Yale Health	Unknown (54/05%)

This basically shows that for a controller, Unknown status has the maximum StateLives and the percentage value in brackets depicts its percentage with that controller among all other status. 
i.e. For Zing health , Unknown status has max lives of 151335 and its coming out be 45.30% of all the StateLives among Zing Health.

Thanks

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

I tried to add it directly in the original code, but it failed, maxx is only used in this one dax can not be effective, in addition, because the follow-up to return the maximum total number of Status names, I suggest using the following Solutions.

If work on base table ,you could add a new column:

StateLive2 = 
CALCULATE (
    SUM ( 'Table'[StateLive] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Controller] = EARLIER ( 'Table'[Controller] )
            && 'Table'[Status] = EARLIER ( 'Table'[Status] )
    )
)

And if work on a calculate table ,you could use the below dax:

test2 = SUMMARIZE('Table','Table'[Controller],'Table'[Status], "sumvalue", SUM('Table'[StateLive]))

Best Regards

Lucien

[Anonymous]

Thank you @v-luwang-msft 

[v-luwang-msft]

Hi @Anonymous ,

Test the below:

test = 
VAR maxevery =
    CALCULATE (
        MAX ( 'Table'[StateLive] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Controller] = MAX ( 'Table'[Controller] ) )
    )
VAR maxstatus =
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Controller] = MAX ( 'Table'[Controller] )
                && 'Table'[StateLive] = maxevery
        )
    )
VAR total_sum =
    SUM ( 'Table'[Statelive] )
RETURN
  maxstatus & " " &    FORMAT (DIVIDE ( maxevery, total_sum ), "0.00%" )

A little adjust to your base data:

Output Result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Anonymous]

@v-luwang-msft This is almost there. There is just 1 more thing required. 

The data in the database is like this :

So first step is to group it by Controller, Status and do sum of Statelives.
On the resulting view, the above code can work perfectly fine.

Is there a way to add this grouping also within this code? 

Thanks,

[Anonymous]

Thanks @Samarth_18 , 

The code works fine, but there is 1 issue. The requirement is not to capture "Unknown" but the any status which has maximum StateLives within that controller. 

In my above example, all 3 controllers had max Statelives for Unknown, but in reality, the code should automatically detect which status has max StateLives and then it just shows that status in winning percent.


Best, Ritin

[Samarth_18]

Hi @Anonymous ,

You can try below code:-

Win_per = 
VAR total_sum =
    SUM ( Winning_percenatge[Statelives] )
VAR unknown_statelive =
    CALCULATE (
        SUM ( Winning_percenatge[Statelives] ),
        Winning_percenatge[Status] = "Unknown"
    )
RETURN
    "Unknown " & FORMAT ( DIVIDE ( unknown_statelive, total_sum ), "0.00%" )

Thanks,

Samarth

[Anonymous]

@Samarth_18 
Below is another example of what I am trying to achieve:

Sample of data table I have: 

The Output should be:

That is, for each controller, I want to check the status which has maximum lives and then concatenate that status label with the percentage of its statelives. 

Below is an example of how to get the percentage value for each controller:

Percent calc for Controller = "Health Care Service Corporation"
(5518072) / (341426 + 888621 + 655404 + 5518073)