Forum Discussion

runatthakorn's avatar
runatthakorn
Regular Visitor
4 years ago
Solved

Row Subtotal from Distinctcount is incorrect

Hi Everyone,

 

I have used Subtotal from Distinctcount in the table and the result is incorrect. I used 2 measures for the calculation below.

 

 

visit(cal) = DISTINCTCOUNT(Spending[date])

 

 

 

Visit = VAR _table = SUMMARIZE(Spending, Spending[member_number], "distinctcount", DISTINCTCOUNT(Spending[date]))
RETURN
SUMX(_table, [visit(cal)])

 

 

The result of subtotal should be (14+24+46) = 84 but in the table shown 83. Please help me to fix it.

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi runatthakorn 

    you may have a similar id in rows between two categories, So as your formula is distinct count it counts it only once.

    can we have a your data set to verify ?

     



    If this post helps, then please consider accepting it as the solution to
    help other members find it faster, and give a big thumbs up. 🙂

     

    • runatthakorn's avatar
      runatthakorn
      Regular Visitor
      4 years ago

      Hi FerhatLyes,

       

      Thank you for your reply and for the sample of data set as below.

       

       

      I want to use distinct count of date group by member_number and group by branch_name.

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi runatthakorn ï¼Œ

    Refer the below ,according your provided,similar question:

    2+2 ,get  total:3

    You need to create a new measure:

    test = if(HASONEVALUE(Spending[branch_name]),[visit(cal)],SUMX(DISTINCT(Spending[branch_name]),[visit(cal)]))

    Output:

     

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


    Best Regards

    Lucien