Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
a68tbird
Resolver II
Resolver II

Distribute Amount Across Pivot Table Rows

Hello All -

I need to allocate a set admin fee ($5500) per quarter across the rows in my pivot table (product categories) relative to the quantity of products sold. The pivot table is filtered by quarter (period).  So, let's say for simplicity, 4 product categories this quarter, each with 100 products sold. The admin fee allocation would be $13.75 per product (400 products sold in that quarter * $13.75 = $5500). Previous quarter had 5 product categories, with various quantities sold. The admin fee is distributed proportionately across the product categories to still add up to $5500.

How could I translate this into a measure? 

 

Thanks very much!

2 ACCEPTED SOLUTIONS

Try using an  ALL() filter instead on your type column

View solution in original post

Yup! Actually just figured that out as I received your reply! 🙂 Thanks very much. Works like a charm.

 

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Thank you @lbendlin.  You can download a sanitized sample data model here

 

The outcome that I'm looking for should replicate these screenshots below.  These are two different date ranges (Q1 2021 and Q1 2022). 

 

a68tbird_0-1654521812724.png

 

a68tbird_1-1654521857078.png

 

 

 

Thank you for providing the sample data. That helps a lot with proposing a potential solution. See attached.

Thanks. I realize this is a PowerBI forum, but was hoping that the solution would translate across to Excel.  REMOVEFILTERS is not a valid function in Excel.  Would you happen to know an equivalent way to do this so it works with my posted data sample?

Try using an  ALL() filter instead on your type column

Yup! Actually just figured that out as I received your reply! 🙂 Thanks very much. Works like a charm.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors