topic Re: DAX Distinct Count Grand Total Showing incorrect value in DAX Commands and Tips

DAX Distinct Count Grand Total Showing incorrect value

Hijbul_Bari — Mon, 14 Feb 2022 09:24:19 GMT

Why my Calculation didnt give correct Grand Total. Grand Total will be 33 but showing 44. Please help.

SUMX(

VALUES(data[Date]),

CALCULATE(DISTINCTCOUNT(data[Wrap-up]),DATESINPERIOD(data[Date],MAX(data[Date]),-2,DAY))

)

Same problem happened even I remove Distinct Count. Grand Total will be 38 but showing 48.

SUMX(VALUES(data[Date]),

CALCULATE(COUNT(data[Wrap-up]), DATESINPERIOD(data[Date],MAX(data[Date]),-2,DAY))

)

How do I get the correct Grand Total.

Re: DAX Distinct Count Grand Total Showing incorrect value

ValtteriN — Mon, 14 Feb 2022 09:37:20 GMT

Hi,

The way total calculation works is a bit peculiar. I recommend reading this article to understand what is happening here: https://www.sqlbi.com/articles/summing-values-for-the-total/

You can circumvent this behaviour by for example using this kind of pattern: IF(SELECTEDVALUE(data[Date])=BLANK(),
CALCULATE(DISTINCTCOUNT(data[Wrap-up]),DATESINPERIOD(data[Date],MAX(data[Date]),-2,DAY)),

SUMX(

VALUES(data[Date]),

CALCULATE(DISTINCTCOUNT(data[Wrap-up]),DATESINPERIOD(data[Date],MAX(data[Date]),-2,DAY))

)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: DAX Distinct Count Grand Total Showing incorrect value

tamerj1 — Mon, 14 Feb 2022 09:51:15 GMT

Hi Hijbul_Bari
Don't think of the grand total as a grand sum. It is acually evaluated seperately same as any other cell in the matrix or pivot table. It really depends on your data to realize what is the problem. If possible please share sample data and will assest you. Thanks and have a great day!

Re: DAX Distinct Count Grand Total Showing incorrect value

Hijbul_Bari — Mon, 14 Feb 2022 09:57:24 GMT

Its return last value 11 but not grand sum.

Re: DAX Distinct Count Grand Total Showing incorrect value

Hijbul_Bari — Mon, 14 Feb 2022 09:59:14 GMT

Date Wrap-up

2/1/2022	Subscription_ACCBalance_QFTR
2/1/2022	Package_AKASH Lite Plus_RFTR
2/1/2022	Package_AKASH LITE_RFTR
2/1/2022	Subscription_Service Act_QFTR
2/1/2022	Package_AKASH Lite Plus_RFTR
2/1/2022	Camp Offer_Referral Offer_RFTR
2/2/2022	Content_Specefic Channel_CFTR
2/2/2022	Subscription_ACCBalance_QFTR
2/2/2022	Package_AKASH STANDARD_RFTR
2/2/2022	Camp Offer_Referral Offer_QFTR
2/2/2022	Error Code_E17-0_CFTR
2/3/2022	Package_AKASH STANDARD_RFTR
2/3/2022	Package_AKASH STANDARD_QFTR
2/3/2022	Package_Package Info_QFTR
2/3/2022	Package_AKASH LITE_QFTR
2/4/2022	Subscription_ACCBalance_QFTR
2/4/2022	Content_Specefic Channel_QFTR
2/4/2022	Info Update_HelpPage_CFTR
2/4/2022	Purchase_General Process_QFTR
2/4/2022	Package_AKASH STANDARD_RFTR
2/4/2022	Recharge_bKash_QFTR
2/4/2022	Camp Offer_Feb Recharge_QFTR
2/4/2022	Subscription_Classific_QFTR

Re: DAX Distinct Count Grand Total Showing incorrect value

Anonymous — Thu, 17 Feb 2022 02:56:11 GMT

Hi Hijbul_Bari ,

Here are the steps you can follow：

1. Create measure.

Count_measure = COUNTX(FILTER(ALL(data),'data'[Date]<=MAX('data'[Date])&&'data'[Date]>MAX('data'[Date])-2),[Wrap-up])

Sum_Total = var _table=SUMMARIZE('data','data'[Date],"_value",[Count_measure]) return IF(HASONEVALUE('data'[Date]),[Count_measure],SUMX( _table,[_value]))

2. Result:

Best Regards,

Liu Yang

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