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!
