Distinct Count measure Grand Total is showing Wrong Value

[Anonymous]

I have One Fact table with columns

Provider Key | Subgroup Key |

3 Dimentions as below

Sub Group table

Sub Group Key | MARKET_DSC | Purchaser name | Group | Subgroup

Fee Schedule table

FEE_SCHEDULE_KEY | fee schedule id | fee schedule

Provider table

Provider key|provider id

I am calculation distinct count of provider key's based on grouping of  [MARKET_DSC + Purchaser name],Group,Subgroup,Fee schedule ID, Fee Schedule

Measure showing Correct disntict count for all row context but showing Wrong grand total at the end of TABLE.

[Cmcmahan]

We can't see the image you linked.

Is the total count too high, or too low?  If it's too high, it might be unfiltering values that you aren't showing in your data or double counting.  If it's too low, it could be filtering out values you meant to be counted. 

[Anonymous]

please check attached image for the same
image of desktop

[Cmcmahan]

Which total value is wrong? Cnt Providers?  Should it be calculated as (72699+517+511+480+392+389+347....) or some other way?

It looks like your top row is an unfiltered amount, so it has all providers counted there, and each of the rest are filtered values under that.  The total is just counting all providers once again, so you get the same value as your first row.  

You may want to use a Matrix instead of a table visualization for this data.

[Anonymous]

 it Should be calculated as (72699+517+511+480+392+389+347....) like this.

If i dragged my measure in card it is showing me 72699.

It is just counting distinct values from overall table and return that measure value(72699).

I want result as sum of all providers.

@Cmcmahan 

[Michiel · Accepted solution]

Create another measure

Cnt by Entity = SUMX(VALUES(table[Legal Entity]), [cnt providers])

Assuming table[Legal Entity] is the item in the first column of your table. The SUMX forces [cnt providers] to be calculated by entity, and then summed.

Rememeber, the total row shows the result of the measure in the context of all detail rows together, not the sum of all the results. So with a simple distinctcount, it is expected that the values don't add up.

[Anonymous]

@v-qiuyu-msft  @Greg_Deckle @Cmcmahan 

could you please provide me solution.