Aggregating data by defying the logic of Group by / Summarize / Hierarchy

[Anonymous]

Hi,

I am trying to solve a problem which would have been fairly easy to solve on an excel sheet via manual intervention. But on Power BI it seems next to impossible.

The database is a dump of patient-wise component-wise billing across several episodes (single table without any relationships).

An episode here refers to a hospital admission - and all hospital services that are billed to the patient within this admission can be found in this dump. Each episode is identified by an 'EpisodeNumber' which is the reference point for all calculations.

I am trying to arrive at the 'Desired Output' shown in pic below:

[mahoneypat]

Here is a measure expression that shows one way to do it.

NewMeasure =
VAR thistotal =
    SUM ( Episodes[BilledAmount] )
VAR S855andS991 =
    CALCULATE (
        SUM ( Episodes[BilledAmount] ),
        Episodes[ServiceName] IN { "S855", "S991" }
    )
VAR overalltotal =
    CALCULATE ( SUM ( Episodes[BilledAmount] ), ALL ( Episodes[ServiceName] ) )
VAR hassurgery =
    COUNTROWS (
        INTERSECT (
            { "S855", "S991" },
            CALCULATETABLE (
                DISTINCT ( Episodes[ServiceName] ),
                ALL ( Episodes[ServiceName] )
            )
        )
    ) > 0
VAR result =
    IF ( hassurgery, overalltotal * thistotal / S855andS991, thistotal )
VAR hassurgerybutnotsurgery =
    NOT ( SELECTEDVALUE ( Episodes[ServiceName] ) IN { "S855", "S991" } )
        && hassurgery
RETURN
    IF ( hassurgerybutnotsurgery, BLANK (), result )

Pat

[Anonymous]

Hi @mahoneypat 

First of all thank you so much for devoting your time to this query. Unfortunately, my problem in executing the above mentioned solution is that I am dealing with more than 2,000 unique surgical procedures. So using IN{} on a list of 2,000 names is something I am not able to wrap my head around.

I even created a new column (in a new table) with the list of those 2,000 names and tried to apply the IN{} function to all values in that column but I guess it doesn't work that way in PBI.

Would you have an idea on how to go about given this new piece of information?

[mahoneypat]

You could make a Groups column to combine all those ServiceNames under a common label.  Or event better you can add another table to your model that lists all the procedures/ServiceNames with a subgroup label.

Pat

[Anonymous]

Hi Pat,
I am still struggling with this as I am new to PBI - would be awesome if you could help me out.

So I have created two more (single column) tables:
1. one with DISTINCT function on all episodes that contain surgeries in them. It is titled T. Surgical Episodes
2. another one with DISTINCT function on names of all surgeries. It is titled T. Distinct Surgeries

Also, in my data the C4 and C5 refer to keywords "PACKAGE" and "SURGERY" in column name "Service Head." Also, in my formula SUM of BilledAmount is a measure called "Baseline Revenue Non-COVID." The metric for calculating a patient's total bill amount is "Baseline Revenue Non-COVID" at the 'Episode No' level.

I tried to align the measure you suggested to these new tables but something's missing. I am attaching the formula (you can also download sample file via the URL):sample file MB_PBD 

Measure_mahoneypat = 
VAR IndividualSurgeryRevenuePerPatientBill = 
    CALCULATE([Baseline Revenue Non-COVID], 
        FILTER('Fact Table', 'Fact Table'[Service Item] = 
            SELECTEDVALUE('T. Distinct Surgeries'[Surgery Name])))

VAR TotalSurgeryRevenuePerPatientBill = 
    CALCULATE([Baseline Revenue Non-COVID], 'Fact Table'[Service Head] IN{"PACKAGE", "SURGERY"})

VAR TotalPatientBill = 
    CALCULATE([Baseline Revenue Non-COVID],
        FILTER('Fact Table','Fact Table'[Episode No] = 
            SELECTEDVALUE('T. Surgical Episodes'[Episode No])))

VAR hassurgery = TotalSurgeryRevenuePerPatientBill > 2

VAR doesnothavesurgery = 
    CALCULATE([Baseline Revenue Non-COVID], 
        FILTER('Fact Table', 
        NOT('Fact Table'[Episode No] = SELECTEDVALUE('T. Surgical Episodes'[Episode No]))))

RETURN 
IF(hassurgery,IndividualSurgeryRevenuePerPatientBill / TotalSurgeryRevenuePerPatientBill * TotalPatientBill,doesnothavesurgery)

@mahoneypat 

[mahoneypat]

I don't think you should need those extra tables.  Can you provide an example of the desired output from the Excel file you linked (using Excel if needed)?  And confirm it is a table/matrix visual you want on a report page with that output.

Pat

[Anonymous]

Hi Pat,

Although I am working on this on a PBD file (screenshot below), I have added a tab in the excel file for the desired output.

Since the last post, I have been able to achieve close to 95% accuracy by modifying the formula.
Although I am still not able to:

1. summarize the measure
2. Add "NA" or something to episodes where no surgery exists.

New formula is as below:

Measure_mahoneypat = 
VAR IndividualSurgeryRevenuePerPatientBill = 
    CALCULATE([Baseline Revenue Non-COVID], 
        FILTER('Fact Table', 'Fact Table'[Service Item] = 
            SELECTEDVALUE('T. Distinct Surgeries'[Surgery Name])))

VAR TotalSurgeryRevenuePerPatientBill = 
    CALCULATE([Baseline Revenue Non-COVID], 'Fact Table'[Service Head] IN{"PACKAGE", "SURGERY"}, 
    REMOVEFILTERS('T. Distinct Surgeries'[Surgery Name]))

VAR TotalPatientBill = 
    CALCULATE([Baseline Revenue Non-COVID],
        REMOVEFILTERS('T. Distinct Surgeries'[Surgery Name]))

VAR hassurgery = TotalSurgeryRevenuePerPatientBill > 2

VAR doesnothavesurgery = 
    CALCULATE([Baseline Revenue Non-COVID], 
        FILTER('Fact Table', 
        NOT('Fact Table'[Episode No] = SELECTEDVALUE('T. Surgical Episodes'[Episode No]))))

RETURN 
IF(hassurgery,IndividualSurgeryRevenuePerPatientBill / TotalSurgeryRevenuePerPatientBill * TotalPatientBill,doesnothavesurgery)

 @mahoneypat

[mahoneypat]

I think this matches your desired output.  Please confirm.

NewMeasure =
VAR thistotal =
    SUM ( 'Fact Table'[BilledAmount] )
VAR thiscategory =
    MIN ( 'Fact Table'[ServiceCategory (= Service Head)] )
VAR SurgeryTotal =
    CALCULATE (
        SUM ( 'Fact Table'[BilledAmount] ),
        ALLEXCEPT ( 'Fact Table', 'Fact Table'[Episode No] ),
        'Fact Table'[ServiceCategory (= Service Head)] = "Surgery"
    )
VAR overalltotal =
    CALCULATE (
        SUM ( 'Fact Table'[BilledAmount] ),
        ALL ( 'Fact Table'[ServiceName (= Service Item)] )
    )
VAR hassurgery = SurgeryTotal > 0
VAR result =
    IF ( hassurgery, overalltotal * thistotal / SurgeryTotal, thistotal )
VAR hassurgerybutnotsurgery = hassurgery
    && thiscategory <> "Surgery"
RETURN
    IF ( hassurgerybutnotsurgery, BLANK (), result )

Pat

[Anonymous]

So three issues with this measure:

1. The total is not adding up by a huge margin (-857k vs 0.66 Bn)

2. This output is segregating surgeries revenue and non surgeries revenue within one episode (for e.g. 155826/20/1104 appears as two seperate rows) while I want every non-surgery revenue within one episode to be merged with the surgery revenue. So, the entire revenue for 155826/20/1104 should be allocated to S7.

3. In the PBD output, I am still not able to summarize the measure, which is crucial to my analysis (because the next step is to find out which are the top revenue generating surgeries)

[v-yangliu-msft]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Measure_ServiceName =
SWITCH(
    TRUE(),
    MAX('Table'[EpisodeNumber])="2021/111"&&MAX('Table'[ServiceName])="S991","S991",
    MAX('Table'[EpisodeNumber])="2021/111"&&MAX('Table'[ServiceName])="S855","S855",
    MAX('Table'[EpisodeNumber])="2020/999"&&MAX('Table'[ServiceName])="S43","NA",
    BLANK())

Measure_Billedamount =
var _S991=SUMX(FILTER(ALL('Table'),'Table'[ServiceName]="S991"),[BilledAmount])
var _S855=SUMX(FILTER(ALL('Table'),'Table'[ServiceName]="S855"),[BilledAmount])
var _2021all=SUMX(FILTER(ALL('Table'),'Table'[EpisodeNumber]="2021/111"),[BilledAmount])
var _2020all=SUMX(FILTER(ALL('Table'),'Table'[EpisodeNumber]="2020/999"),[BilledAmount])
return
    SWITCH(
        TRUE(),
        [Measure_ServiceName]="S991",
        DIVIDE(_S991,
        _S991+_S855) * _2021all,
        [Measure_ServiceName]="S855",
        DIVIDE(_S855,
        _S991+_S855)*_2021all,
        [Measure_ServiceName]="NA",_2020all,
        BLANK()
        )

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi @v-yangliu-msft 

First of all thank you so much for devoting your time to this query. Unfortunately, my problem in executing the above mentioned solution is that I am dealing with more than 2,000 unique surgical procedures.

[Anonymous]

Reaching out to the Leaderboard @amitchandak @a9126030767 @alexkolokolov @Greg_Deckler @selimovd @Fowmy @mahoneypat @parry2k @Jihwan_Kim @edhans