Distribute Amount Across Pivot Table Rows

[a68tbird]

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!

[lbendlin · Accepted solution]

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

[lbendlin]

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

[a68tbird]

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). 

[lbendlin]

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

[a68tbird]

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?

[lbendlin · Accepted solution]

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

[a68tbird · Accepted solution]

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