Totals for calculated columns do not add up

[Anonymous]

Good evening, 

I am working on a dashboard that calculates the new total cost based on a category limit chosen from another table. 

Below is the example of what I am working on and the formulas for each column that I have. Totals for each calculated column are not equal to total, but calculated by the totals from other columns. 

Distinct Member Count= calculate(DISTINCTCOUNT(Cost[Member ID]), Cost)

Cost per Distinct Member= divide(sum(cost[paid],distinctcount(cost[Member ID])

Limited Cost per Member= if([Cost per Distinct Member]<'Category Limits'[Limit],[Cost per Distinct Member],'Category Limits'[Limit]) ---- Capping the medical cost by selecting a value from another table

New Total Cost= [Distinct Member Count]*[Limited Cost per Member]

Difference in Total Cost= sum(Cost[Total Cost])-[New Total Cost])

Any help is appreciated. Worked on it all day, but couldn't find the right formula.

[dax]

Hi okaplan, 

Agree with @TomMartens , if possible, could you please inform me more detailed information (such as your sample data and your expected output)? You could use  simple data sample to replace your real data and upload your pbix file on forum by OneDrive or other place we could access. Then I will help you more correctly.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Anonymous ,

without sample data it's almost impossible to detect what's going on, even more as there seem to be some issues with the parenthesis, at least for the "Cost per Distinct Column", whenever DISTINCTCOUNT is involved, it really hard to discover issues just by looking at formulas, without having any clues about the data distribution. 

So you might consider to create a pbix that contains sample data, btu still represents your data model and also allows to "recreate the issue", upload the file to onedrive or dropbox and share the link, if you are using Excel to create the sample data, upload the xlsx as well.

I note one thing, the measure "Cost per Distinct Member", is using distinctcount(...), whereas the measure "Distinct Member Count" is using CALCULATE(DISTINCTCOUNT(...) , 'cost'), maybe the missing filtertable can mess up the numbers.

How do you calculate the correct number "Limited Cost per member"?

Regards,

Tom