topic Interaction causes invalid calculation in DAX Commands and Tips

Interaction causes invalid calculation

Anonymous — Thu, 04 Jun 2020 21:32:45 GMT

Somehow when I'm clicking on bars it messes up my % calculation. Do I need to alter the calcuation?

Here is what I've done:

Created custom column to determine if a claim is electronic or paper using

Claim Type =if[#" Process ERA"] = "No" and [#" Process Paper"] = "No" and [#" Payer Name"]<>"PATPAY" then "Paper" else "Electronic"

I then created a measure to determine what % of claims are electronic

% Electronic= CALCULATE(SUM('Table'[ Claim Count]),'Table'[Claim Type]="Electronic")/CALCULATE(SUM('Table'[ Claim Count]))

I then created a table showing % of electronic and total number of claims by client. I also created a side by side bar chart showing paper vs electronic by month. If I click on the bar that represents Paper claims for any of those months I get a % greater than 100 which is not possible.

I broke out the calculation above to get just electonic count and total count. As you can see electronic is 703,000 while total is 206,000. I also included a row of data to show how the data is coming in. It seems like the date interaction is causing issues for some reason because the calculation works fine when i dont click on the bars.

Re: Interaction causes invalid calculation

stevedep — Fri, 05 Jun 2020 04:32:21 GMT

Since the counts are off after slicing it shows that it's not the calculation. Can't be the calculated column. Best to slice to a small selection and try to understand why these rows remain after slicing.

Re: Interaction causes invalid calculation

Anonymous — Sun, 07 Jun 2020 20:20:32 GMT

[Total Claims] = SUM ( 'Table'[ Claim Count] ) [% Electronic] = var __electronicClaims = CALCULATE ( [Total Claims], KEEPFILTERS( 'Table'[Claim Type] = "Electronic" ) ) var __totalClaims = [Total Claims] return DIVIDE( __electronicClaims, __totalClaims )

Please learn about how filters interact with each other when you use them in CALCULATE.

Best