Calculate Invoice Allocations with Multiple Levels of Filtering

bonitatem — Tue, 19 Mar 2024 19:02:56 GMT

I have two tables - one with software license usage by department and user; the second with invoice data for the different licenses. I'm trying to allocate the invoice costs to the different departments based on license usage.

I'd like to take the number of users that are assigned a specific license within a department and divide by the total number of users who use that license in the organization and then multiply by the sum of the invoiced $'s for that license. Then I want to sum those calculated dollars for all licenses to get total license cost by department, but also be able to drill down to see the costs that specific users or licenses are accruing for a team.

Here's some sample data:

Department	DisplayName	License
1000	User227	Microsoft - Business Premium
1000	User288	DocuSign
1000	User288	Microsoft - Business Premium
1000	User431	DocuSign
1000	User431	Microsoft - Business Premium
1001	User294	Microsoft - Business Premium
1001	User294	Microsoft - Power BI Pro
1001	User325	Microsoft - Business Premium
1001	User368	Microsoft - Power BI Pro

Month	Total to Allocate	Vendor / Invoice Type
45292	100	DocuSign
45292	200	Microsoft - Business Premium
45292	25	Microsoft - Power BI Pro
45323	100	DocuSign
45323	175	Microsoft - Business Premium
45323	50	Microsoft - Power BI Pro

If I were doing this in Excel for each license I would use a combination of COUNTIF and SUMIF to generate a table like this below (though I'd want to be able to drill down one more layer to see cost/user).

Department	Microsoft - Business Premium	DocuSign	Microsoft - Power BI Pro	Total
1000	225	200	0	425
1001	150	0	75	225

For reference, here's one of the formulas in Excel: =(COUNTIFS(Licenses_All[Department],$A3,Licenses_All[License],"Microsoft - Business Premium")/COUNTIF(Licenses_All[License],"Microsoft - Business Premium"))*SUMIF(Software_Assurance_Allocations[Vendor / Invoice Type],"Microsoft - Business Premium",Software_Assurance_Allocations[Total to Allocate])

I've tried a number of different DAX expressions to replicate, but here's the current iteration:

Allocation_Amount = DIVIDE(COUNT(Licenses_All[Department]),CALCULATE(COUNT(Licenses_All[Department]),ALLSELECTED(Licenses_All[Department]))) * SUM('Software_Assurance_Allocations'[Total to Allocate])

This expression correctly calculates the allocation for each license by department, but provides the incorrect results for the Totals column (it's aggregating the data instead of summing the data for each individual license - I'm not sure how to add that into the expression) and also doesn't work when I drill down to see individual users (the DIVIDE half of the expression is returning 1, so it's displaying the total license cost).

Thank you for your help!

Re: Calculate Invoice Allocations with Multiple Levels of Filtering

ThxAlot — Tue, 19 Mar 2024 22:06:28 GMT

topic Calculate Invoice Allocations with Multiple Levels of Filtering in DAX Commands and Tips

Calculate Invoice Allocations with Multiple Levels of Filtering

Re: Calculate Invoice Allocations with Multiple Levels of Filtering