PowerPivot create Slicer based off measure calculation

GiantPygmy

New Member
Joined
Jul 26, 2016
Messages
6
I need I have a table that has medication on it and a transaction amount. There will be multiple entries due to adjustments and insurance for one medication and one order that went out. I have a measure that basically sums those up and then on the pivot table it will show correctly at the patient and order level. (below is example data table being used) The vendor Ml per box and Vendor Cost are just brought in from a dim table for math purposes and should not be summed (only need the amount it each once). I have in in included the measures I'm using below as well. They are insistent they want a slicer that simply says "Positive" or "Negative" that will filter the pivot table by ProfitPerBox (ProfitPerBox < 0 then negative else positive) to only show negative or positive amounts. Any help on how to achieve this within Excel PowerPivot would be greatly appreciated.

Pivot Table is:
Rows: Account, Service Date,WorkOrderNumber,Payor,Product
Values: RevenuePerBox,MaxVendorCost,ProftPerBox

MaxBoxes:=MAX(F_REVENUE2[Boxes])
TotalRevenue:=SUM(F_REVENUE2[TransAmount])
MaxVendorCost:=MAX(F_REVENUE2[Vendor Cost per Box])
RevenuePerBox:=[TotalRevenue]/[MaxBoxes]
ProftPerBox:=[RevenuePerBox]-[MaxVendorCost]

Service DateAccountWorkOrderNumberPayorQty in MLProductTransAmountVendor Ml per BoxVendor Cost per Box
9/4/2018111444444MEDICARE REGION D - NEW360Ipratropium-Albuterol0.31903.06
9/4/2018111444444MEDICARE REGION D - NEW120Budesonide0.566060
9/4/2018111444444MEDICARE REGION D - NEW360Ipratropium-Albuterol289.12903.06
9/4/2018111444444MEDICARE REGION D - NEW360Ipratropium-Albuterol-272.32903.06
9/4/2018111444444MEDICARE REGION D - NEW120Budesonide-201.86060
9/4/2018111444444MEDICARE REGION D - NEW360Ipratropium-Albuterol-0.34903.06
9/4/2018111444444MEDICARE REGION D - NEW120Budesonide413.66060
9/4/2018111444444MEDICARE REGION D - NEW120Budesonide-4.246060
10/8/2018222555555MEDICARE REGION D - NEW360Ipratropium-Albuterol-273.52903.06
10/8/2018222555555MEDICARE REGION D - NEW360Ipratropium-Albuterol-0.29903.06
10/8/2018222555555MEDICARE REGION D - NEW360Ipratropium-Albuterol289.12903.06
10/8/2018222555555MEDICARE REGION D - NEW360Ipratropium-Albuterol-0.31903.06
11/28/2018222666666MEDICARE REGION D - NEW360Ipratropium-Albuterol289.12903.06
11/28/2018222666666MEDICARE REGION D - NEW360Ipratropium-Albuterol-0.31903.06
11/28/2018222666666MEDICARE REGION D - NEW360Ipratropium-Albuterol-273.52903.06
11/28/2018222666666MEDICARE REGION D - NEW360Ipratropium-Albuterol-0.29903.06
3/27/2018333777777MEDICARE REGION D - NEW90Ipratropium-Albuterol-0.07903.06
3/27/2018333777777MEDICARE REGION D - NEW90Ipratropium-Albuterol-68.8903.06
3/27/2018333777777MEDICARE REGION D - NEW120Brovana-656.83120562.74
3/27/2018333777777MEDICARE REGION D - NEW120Budesonide413.66060
3/27/2018333777777MEDICARE REGION D - NEW120Budesonide-3.86060
3/27/2018333777777MEDICARE REGION D - NEW120Budesonide-223.646060
3/27/2018333777777MEDICARE REGION D - NEW90Ipratropium-Albuterol72.28903.06
3/27/2018333777777MEDICARE REGION D - NEW120Brovana1224.43120562.74
3/27/2018333777777MEDICARE REGION D - NEW120Brovana-11.35120562.74

<tbody>
</tbody>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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