Row Subtotal from Distinctcount is incorrect

[runatthakorn]

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.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[v-luwang-msft · Accepted solution]

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

[Anonymous]

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]

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.