Power Pivot Measure Assistance

[brandon12]

I am attempting to become more efficient with a process I do monthly by utilizing Power Pivot (which is new to me) verse excel and multiple formulas. All of my data is in one table and I'm attempting to replicate the formulas that have been created for use in excel for use in Power Pivot. However, I've learned that this is not an easy process for a beginning Power Pivot user so I'm turning to some experts for assistance.

My table headers are as follows:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]Service Date[/TD]
[TD="align: center"]ClientName[/TD]
[TD="align: center"]Service Type[/TD]
[TD="align: center"]CPT Code[/TD]
[TD="align: center"]Staff Name[/TD]
[TD="align: center"]Merged Duration[/TD]
[/TR]
</tbody>[/TABLE]

The following formulas are the one's I'm attempting to replicate as a measure in Power Pivot:

ANCIL:
=SUM(IF(((D:D=70000)+(D:D=72000))>0,IF(F:F>30,30,F:F),IF(((D:D=71000)+(D:D=73000))>0,IF(F:F>60,60,F:F),IF((D:D=75000),IF(F:F>15,15,F:F)))),IF((D:D=90899),F:F))

TRAVEL:
=SUM(SUMIF(D:D,{"H0046","46","74000"},F:F))

FA/TP:
=SUM(IF(((D:D="H0032")+(D:D="H0031"))>0,INT(F:F/15)*15))

SKILLS:
=SUM(IF(((D:D="H2017")+(D:D="H2014")+(D:D=90882)+(D:D="S5110"))>0,INT(F:F/15)*15))

THERAPY:
=SUM(IF(((D:D=90832)+(D:D=90834)+(D:D=90837)+(D:D=99349)+(D:D=90847)+(D:D=90846))>0,IF(F:F>=75,75,IF(F:F>=60,F:F,IF(F:F>=53,60,F:F)))))

DA:
=SUM(IF(((D:D=90791)+(D:D=99343)+(D:D=99000))>0,IF(F:F>120,120,F:F)))

SDQ/CASII:
=SUM(SUMIF(C:C,{"SDQ","CASII"},F:F))


Thanks in advance!

- Brandon

 

[macfuller]

Interesting. It's a bit hard to read the nested IFs in your Ancil column but offhand it seems like if a bunch of matching conditions are true you want to total the equivalent Merged Duration values, with a ceiling on the Duration values of 30 or 60 depending on the code.

On a simpler basis for the travel category, something like

Travel Duration :=CALCULATE (
    SUM ( MyTable[Merged Duration] ),
    FILTER (
        MyTable,
        MyTable[CPT Code] = "H0046"
            || MyTable[CPT Code] = "46"
            || MyTable[CPT Code] = "74000"
    )
)

Hopefully that works, and gives you the concept to build the other measures.

Ultimately if you want to filter by conditions and have slicers you could use Power Query to create a new column to indicate which category the CPT code falls into like Ancil or Travel (assuming they're mutually exclusive of course), then have a simpler generic SUM statement and use a slicer on those column values to only include what you want.

    AddTypeColumn = Table.AddColumn(PreviousStep, "Code Type", each
         if [CPT Code] = "H0046" or [CPT Code] = "46" or [CPT Code] = "74000" then "Travel"
        else if ... then "Ancil"
        else if ...
        else "", type text),

 

[brandon12]

Thanks. This is a start. How can I add in a filter for Staff Name as the code you provided gives me a sum of all travel and places the same value in all respective Staff Name rows. I attempted the following code, but it doesn't work:

CALCULATE (SUM (Visits[Merged Duration]),
FILTER (Visits, Visits[CPT Code] = "H0046"
|| Visits[CPT Code] = "46"
|| Visits[CPT Code] = "74000"),
FILTER(Visits,Visits[Staff Name])
)

 

[macfuller]

Just to be clear since you mention rows, I assume you were trying to write a measure rather than a column. If your pivot table is showing staff names in the rows the measure I provided should give the appropriate value for each of the staff names. If you're trying to add a column within the data table then as part of the first visits filter you should add the staff name to the filter.

Travel Duration :=
CALCULATE (
    SUM ( MyTable[Merged Duration] ),
    FILTER (
        MyTable,
        ( MyTable[CPT Code] = "H0046"
            || MyTable[CPT Code] = "46"
            || MyTable[CPT Code] = "74000" )
            && Visits[Staff Name]
                = EARLIER ( Visits[Staff Name] )
    )
)

But this hardcodes your value - the column will always show just Travel and the sum for the staff name. You'd have to create a different column for each of your categories. I'd say keep the value as a measure and then it will filter by whatever your row context is... staff name, date, service type.

 

[brandon12]

My preference would be to show each category in its own column and then a total column that adds up all the categories. Below is what I am envisioning:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Staff Name[/TD]
[TD="align: center"]Ancil[/TD]
[TD="align: center"]Travel[/TD]
[TD="align: center"]FA/
TP[/TD]
[TD="align: center"]Skills[/TD]
[TD="align: center"]Therapy[/TD]
[TD="align: center"]DA[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Doe, John[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]390[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Doe, Jane[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]240[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]445[/TD]
[/TR]
</tbody>[/TABLE]

 

[brandon12]

Macfuller,

I stand corrected. Your code posted below works great. I must have had something wrong in my Pivot Table as it is now performing acurately after I started over. Now on to figuring out how to convert the nested IF's (Ancil, FA/TP, SKills, Therapy, DA) into measures. Thoughts?

Travel Duration :=
CALCULATE (
SUM ( MyTable[Merged Duration] ),
FILTER (
MyTable,
( MyTable[CPT Code] = "H0046"
|| MyTable[CPT Code] = "46"
|| MyTable[CPT Code] = "74000" )

 

[brandon12]

With regard to the FA/TP conversion:

Here is what I was using in excel: =SUM(IF(((D:D="H0032")+(D:D="H0031"))>0,INT(F:F/15)*15))

This is what I've come up with so far for a measure:


CALCULATE(
SUM( Visits[Merged Duration]),
FILTER(Visits, Visits[CPT Code] = "H0032"
|| Visits[CPT Code] = "H0031" ),
FLOOR(Visits[Merged Duration],15)
)

However, the FLOOR function is not doing what I anticipated. The Merged Duration for each CPT Code of H0031 and H0032 needs to be rounded down to the nearest number divisible by 15. For example if the Merged Duration was 35 & 47, I would need the function to treat that as a 30 & 45 respectively. I also want to ensure the function does this to each value individually and not after the sum function.

 

[macfuller]

Are the values such as Ancil and Travel mutually exclusive? If so, you should create a new column in Power Query as I provided earlier that gives a value to each row. Then you can use that column as a filter, or put it as a column in your pivot table to give exactly the result table you show above. Then you only need one measure, a simple SUM.

You have to explain the Ancil logic. That's tricky because you want to apply different ceilings to the duration depending on the CPT Code. As far as I can tell you want to sum where the CPT Code = 70000 or 72000 but limit the duration value to MIN(30, [Duration]). Similarly for CPT Codes 71000 or 73000 but limiting the duration to MIN(60, [Duration]) and finally for CPT Code 75000 duration = MIN(15, [Duration].

I make no promises for this measure. Without the data I can't test it. You may need to list the CPT Codes in the FILTER section of the CALCULATE piece if my logic about Duration is wrong. (Your code type should still be created as a new column in Power Query even if the IF statements are a nightmare)

Ancil Duration :=
VAR DurationMax =
    IF (
        MyTable[CPT Code] = "70000"
            || MyTable[CPT Code] = 72000,
        30,
        IF (
            MyTable[CPT Code] = "71000"
                || MyTable[CPT Code] = 73000,
            60,
            IF (
                MyTable[CPT Code] = "75000",
                15,
                0
            )
        )
    )
RETURN
    CALCULATE (
        SUMX (
            MyTable,
            MIN (
                DurationMax,
                MyTable[Merged Duration]
            )
        ),
        FILTER (
            MyTable,
            DurationMax > 0
        )
    )

 

[macfuller]

You want to bill to the quarter hour it seems, so you're right that FLOOR and CEILING won't work. Your Excel logic is fine for DAX. You need to use the SUMX function rather than SUM so the calculation iterates across each row as you want.

FA-TP Duration :=CALCULATE (
    SUMX (
        Visits,
        INT ( Visits[Merged Duration] / 15 ) * 15
    ),
    FILTER (
        Visits,
        Visits[CPT Code] = "H0032"
            || Visits[CPT Code] = "H0031"
    )
)

 

[brandon12]

Yes, the values are mutually exclusive. Your logic about the duration is accurate.