SUMX Help with Price part of Price Volume Mix analysis

[BaldAccountant]

I hope this is a fairly easy request.   It is not very practical to attach a file because all the info I deal with is Protected Health Information.

I am trying to put together a PVM analysis in Power BI to analyse reimbursement.

The price would in effect be reimbursement per case.

Here is my formula for the price impact:

Price Impact =(sumx(values(data[financial class]),[current YTD cases]*[current ytd vs prior ytd reimbursement per case])

or you could think of it as :

SUMX(values(data[product line]),[current YTD volumes]*[change in price])

Financial class is the type of insurance payor - commercial, Medicare, Medicaid, self pay.

The formula calculates the price difference correctly as intended, but it does not display the calculation for each financial class.  I have slicers for year and month and the data is all YTD for the current year and prior year.   

It looks like it is not calculating for any financial class that does not have any cases in the current period.

If I try the formula without sumx it calculates correctly, but the total is not right.

Hopefully I am just making a simple mistake and you guys can help me.

Thanks

[Anonymous · Accepted solution]

Hi @BaldAccountant ,

Please try to create a new table.

And add a new relationship.

Price impact 2 = sumx(values('Table'[Sub-Category]),[CYTD units]*[Delta CY vs PY sales per Unit])

Best Regards,

Wearsky

[BaldAccountant]

https://www.dropbox.com/scl/fi/mx31rxyco1jiufytv8gvs/Test-PWM.pbix?rlkey=xwre0yprauvhw0rvckzlyqiiq&s...

Here is a link to a PBIX with pretend data.   Notice that the price impact for copiers is missing.   It should be $495.33