Power Pivot Measure Assistance

brandon12

New Member
Joined
Sep 12, 2018
Messages
32
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:
ABCDEF
Service DateClientNameService TypeCPT CodeStaff NameMerged Duration

<tbody>
</tbody>

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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
Code:
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.

Code:
    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),
 
Upvote 0
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])
)
 
Upvote 0
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.
Code:
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.
 
Upvote 0
Solution
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:

ABCDEFGH
1Staff NameAncilTravelFA/
TP
SkillsTherapyDATotal
2Doe, John25501560120120390
3Doe, Jane3045254524060445

<tbody>
</tbody>
 
Last edited:
Upvote 0
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" )
 
Upvote 0
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.
 
Upvote 0
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)
Code:
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
        )
    )
 
Last edited:
Upvote 0
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.
Code:
FA-TP Duration :=CALCULATE (
    SUMX (
        Visits,
        INT ( Visits[Merged Duration] / 15 ) * 15
    ),
    FILTER (
        Visits,
        Visits[CPT Code] = "H0032"
            || Visits[CPT Code] = "H0031"
    )
)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top