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>
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,312
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top