subtotals and grandtotal are not correct in matrix when i use DAX DISTINCTCOUNT

[madhushree]

Hi All, I am getting subtotals and grandtotal incorrect in matrix when i use DAX DISTINCTCOUNT. Below screenshot shows the report where i am counting distinct agreements for each partner and sales. But in subtotals also i need the total to be proper. For example 1st total should come as 5 instead of 3. Below are the screenshots of dimensions and fact. Can anyone please help me to get proper total.

This is reportAgreement dimensionfactPartner dimension

[MFelix · Accepted solution]

Hi @madhushree,

Making the calculation of distinct count what is done is based on the context of your pivot table I have added the aggreement i key in the pivot table and in your fact table for partner a you only have 3 agreements althougth they are distributed by 4 sales person that why the total is 3 and not 5.

You can make a measure and then add a new column and use it the count you need.

MEASURE

distinct_count_agree = DISTINCTCOUNT('fact'[agrmnt key])

COLUMN

AGRMT_COUNT = [distinct_count_agree]

Then you will get the expected result.

Regards,

MFelix

[rezaieehsan]

hi i have somehow same problems to get currtect column summurization or rowwise sumurization,

please could u quide me how can i get sum in visual level number from each months.

please check the picture attached