Hi, I want to calculate the total expense per job by month based on the below 2 tables.
For example, the total expense for WM in January is $100 and Job count in January is 2. Hence, $100/2 = $50.
For Product = Non-WM, I would like to take the total expense divide by Total Job Count for Product = WM.
Eg. In March, total expense for Non-WM is $300 and total job count for WM = 1, so the expense per job = $300/1 = $300
Same goes to Product = Non-REF, I would take the total job count for product = REF as denominator.
Go to Solution.
I'm not sure if this is what you are after, and I have had to modify your tables to showcase the measures, but here we go...
With this model:
and these measures:
Sum Expense = SUM('Table A'[Expense])
Job Number = DISTINCTCOUNT('Table B'[Job No.])
Expense per job =
VAR NumJobs = DISTINCTCOUNT('Table B'[Job No.])
DIVIDE([Sum Expense], NumJobs)
Non-REF from REF =
VAR REF =
'Dim Product'[Product] = "REF",
ALL ( 'Dim Product' )
SELECTEDVALUE ( 'Dim Product'[Product] ) = "Non-REF",
DIVIDE ( [Sum Expense], REF )
Non-WM from WM =
VAR WM =
CALCULATE ( [Job Number], 'Dim Product'[Product] = "WM", ALL ( 'Dim Product' ) )
SELECTEDVALUE ( 'Dim Product'[Product] ) = "Non-WM",
DIVIDE ( [Sum Expense], WM )
You get this...
I've attached the sample PBIX file
Proud to be a Super User!Paul on Linkedin.
View solution in original post
Hi @PBI_newuser ,
Why using count of WM for Non-WM and count of REF for Non-REF?
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.