cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hijbul_Bari
Frequent Visitor

DAX Distinct Count Grand Total Showing incorrect value

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))

)

 

image.png

 

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.

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1645066534289.png

 

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

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1645066534289.png

 

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

tamerj1
Super User
Super User

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!

Date Wrap-up

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

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/

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Its return last value 11 but not grand sum.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors