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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
vmurali1
Frequent Visitor

Requesting help with DAX Calculation

Team, 
I have a matrix where i am trying to calculate cost per case. The formula for that would be - Total Product Cost / Total# of Cases. I created two measures. One to sum the total product cost and another to calculate distinct count of the cases and then created a third measure called cost per case utilizing the two measures for numerator and denominator.

Now, if you look at the Category D in the attached snapshot, it calculates the total product cost  of each of the items in the drill down and takes the distinct number of cases where that item had been utilized - Which is correct but, is there any way i can have the denominator constant (Total # of cases by distinct names and by year since i might be adding a slicer for year in the future)? 

For example, if i breakdown the 3164.91, i should be able to see the breakdown of what comprises to 3614.91 rather than an average. In other words, i want the product cost of each item divided by the total # of cases for each names. 

 

Can someone assist me with this ?

 

Thanks in advance

PBIC.PNG

The data structure looks like this

pbic 2.PNG

2 ACCEPTED SOLUTIONS
eliasayy
Impactful Individual
Impactful Individual

Hello @vmurali1 if you want the #cases to be constant by year, you can try to first create a new column year with formula

Year= YEAR(table[case date])

Then create a new measure

# cases by year = Calculate(distinctcount[column you want],allexcept(table,table[Year]))

View solution in original post

eliasayy
Impactful Individual
Impactful Individual

If you want the # cases by year according to column "name" then just do 

 

# cases by year = Calculate(count(table[name],allexcept(table,table[Year],table[name]))

View solution in original post

4 REPLIES 4
vmurali1
Frequent Visitor

Actually, i got the logic and it worked perfect. Thanks for the guidance. 

eliasayy
Impactful Individual
Impactful Individual

Hello @vmurali1 if you want the #cases to be constant by year, you can try to first create a new column year with formula

Year= YEAR(table[case date])

Then create a new measure

# cases by year = Calculate(distinctcount[column you want],allexcept(table,table[Year]))

Thank you for the response but, i want the number of cases to be constant by name (first col) and year. 

eliasayy
Impactful Individual
Impactful Individual

If you want the # cases by year according to column "name" then just do 

 

# cases by year = Calculate(count(table[name],allexcept(table,table[Year],table[name]))

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors