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

 

[brandon12]

Excellent. The FA-TP code result is perfect! Thanks for letting me know my Excel logic works in DAX. I'm going to attempt my hand at the others. I have a feeling Ancil is going to be the issue. I attempted your code (changed the table name to Visits) and received the following message:

"Calculation error in measure 'Visits'[ANCIL]: A single value for column 'CPT Code' in table 'Visits' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I know this probably isn't helpful at all with context or data to visualize it with. I really appreciate all the help!!!

 

[brandon12]

Well I have Travel, Skills, FA-TP, and DA completed, with Therapy and Ancil remaining. Below was where I ended with Therapy, but it does not calculate appropriately. Any ideas what I did wrong?

=CALCULATE(    SUMX( 
        Visits,
        IF(Visits[Merged Duration] >=75,75,[Merged Duration]
            || IF(Visits[Merged Duration] >= 60, [Merged Duration]
                || IF(Visits[Merged Duration] >= 53, 60, [Merged Duration]
                )
            )        
        )
    ),            
    FILTER(
        Visits,
        Visits[CPT Code] = "90832"
            || Visits[CPT Code] = "90834"
            || Visits[CPT Code] = "90837"
            || Visits[CPT Code] = "99349"
            || Visits[CPT Code] = "90847"
            || Visits[CPT Code] = "90846"
        )
)

 

[macfuller]

My bad on the measure. Try this:

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

 

[brandon12]

There was one other value I need to calculate that was not originally in the inital post. I need to calculate a value for per diem which counts the number of occurences of multiple CPT Codes and multiplies them by a specific number - then sum those calculations. Please see the Excel formula below:

=COUNTIF(D:D,"99343")*15+COUNTIF(D:D,"90791")*15+COUNTIF(D:D,"90832")*5+COUNTIF(D:D,"90834")*5+COUNTIF(D:D,"90837")*5+COUNTIF(D:D,"99349")*5+COUNTIF(D:D,"90847")*5+COUNTIF(D:D,"90846")*5+COUNTIF(D:D,"H2014")*5+COUNTIF(D:D,"H2017")*5+COUNTIF(D:D,"90882")*5+COUNTIF(D:D,"H0031")*5

 

[macfuller]

Is your per diem a single unchanging number or does it vary? In other words, you're counting column D "90791" and multiplying by 15. Are you breaking that out by the category codes so that for Travel you only have 3 * 15 90791 codes, or is it always the full sum of all the records in the table and you use that one overall number in every calculation?

Look at this blog post. You would have to add your separate calculations for the multipliers since the example in the post is pure summation - that will take a bit of work. Alternatively you could add a new column in the data model and have each column D value sum to the total count of (say) 90791 and multiply that by the multiplier, then sum the distinct values on that column, but the individual row values would sum across all occurrences of the 90791 instead of just the ones in the current filter context. In other words when you count the 90791 values the full table always has (say) 300 and you'll get a value of 300*15 in each row where D=90791. Any total you calculate in a subsequent filtered environment would still return 4,500 for the 90791 segment of the calculation because every 90791 row has 4,500. Whereas if you were filtering on a measure to only Travel, or only on a certain date, your measure calculation would be (say) 3 * 15 = 45 for 90791 because only 3 rows of 90791 are in the context.

I realize that's probably Greek to you right now but stick with it, you will be rewarded with what you'll be able to do once you get the hang of it.

 

[brandon12]

The per diem rate varies. The sum of occurences for CPT Codes of 90791 & 99343 should be multiplied by 15. The sum of occurences for CPT Codes of 90832,90834,90837,99349, 90847, 90846, H2014, H2017, 90882, H0031 should be multiplied by 5. The following is where I'm at (obviously incorrect).

=CALCULATE(    COUNT([CPT Code])*15,
        FILTER(Visits,
            [CPT Code] = "90791"
                ||[CPT Code] = "99343"),


    COUNT([CPT Code])*5,
            FILTER(Visits,
                [CPT Code] = "90832"
                    ||[CPT Code] = "90834"
                    ||[CPT Code] = "90837"
                    ||[CPT Code] = "99349"
                    ||[CPT Code] = "90847"
                    ||[CPT Code] = "90846"
                    ||[CPT Code] = "H2014"
                    ||[CPT Code] = "H2017"
                    ||[CPT Code] = "90882"
                    ||[CPT Code] = "H0031"))

 

[macfuller]

Close I think. Try

Per Diem:=CALCULATE(COUNT(Codes), FILTER(90791 etc...)) * 15 + CALCULATE(COUNT(Other codes), FILTER(everybody else)...) * 5

 

[brandon12]

Yes! Per Diem now works wonderfully. So my last challenge is finishing the Therapy measure. I feel like it should be similar to the Ancil, but the variable in this one is the Merged Duration as all CPT Codes are treated the same:

[COLOR=#574123]=CALCULATE(    SUMX( [/COLOR]        Visits,
        IF(Visits[Merged Duration] >=75,75
            || IF(Visits[Merged Duration] >= 60, [Merged Duration]
                || IF(Visits[Merged Duration] >= 53, 60, [Merged Duration]
                )
            )        
        )
    ),            
    FILTER(
        Visits,
        Visits[CPT Code] = "90832"
            || Visits[CPT Code] = "90834"
            || Visits[CPT Code] = "90837"
            || Visits[CPT Code] = "99349"
            || Visits[CPT Code] = "90847"
            || Visits[CPT Code] = "90846"
        ) [COLOR=#574123])[/COLOR]

 

[brandon12]

I believe I figured out my issue. It appears to be calculating accurately with the following:

CALCULATE(    SUMX( 
        Visits,
        IF(Visits[Merged Duration] >=75,75,
            IF(Visits[Merged Duration] >= 60, [Merged Duration],
            IF(Visits[Merged Duration] >= 53, 60, [Merged Duration]
                )
            )        
        )
    ),            
    FILTER(
        Visits,
        Visits[CPT Code] = "90832"
            || Visits[CPT Code] = "90834"
            || Visits[CPT Code] = "90837"
            || Visits[CPT Code] = "99349"
            || Visits[CPT Code] = "90847"
            || Visits[CPT Code] = "90846"
        )
)
)

Macfuller, I REALLY appreciate all your assistance on this task. You've gone above and beyong the call of duty with educating me. Thank you!

 

[macfuller]

Glad to help. It's Friday - knock off work.