Forum Discussion

Hijbul_Bari's avatar
Hijbul_Bari
Frequent Visitor
4 years ago
Solved

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],...
DAX
Distinct
grand total
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

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/

 

 

Hijbul_Bari's avatar
Hijbul_Bari
Frequent Visitor
4 years ago

Its return last value 11 but not grand sum.