Total Column Sum is not equal

[Anonymous]

Hi everyone! I've been stuck with this, hope you can help me. Thanks! The Total Sum should be 475.

[amitchandak]

Share the formula you are using as of now.

[Anonymous]

B2B OUTLET BUYING = CALCULATE(DISTINCTCOUNT([Outlet Name]),'TOTAL SALES DATA'[Billing Type]="ZRSS") Here

[danextian]

DISTINCTCOUNT() counts the unique values. In your total, it counts the unique values in [Outlet Name] in the current filter context which is [Billing Type]="ZRSS" and two or more tags may be sharing the same Outlet Name and that Outlet Name is counted as one only in the total. 

[Anonymous]

Hi danextian,

Ohhh. I get it. Is the a way to fix this? I need to count distinct outlets with the billing type of ZRSS. Row values are correct its just that the total of the column did not match to the individual values in the rows.

Thanks

[danextian]

Hi @Anonymous ,

You may try @Anonymous 's formula. SUMMARIZE() creates a virtual table of count of unique outlet name for each tag  within the measure itself and SUMX sums up the count. Or you may create a calculated column that concatenates tag and outlet name columns and use that in your distinctcount measure instead. 

[dethompson97]

Can you also share your data model?  What is [Outlet Name]?  By convention, it should be a measure but I am not sure it is.

Thanks!

DAwn

[Anonymous]

Here,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We suggest you to delete your shared report link in your reply if it contain any confidential information, We can also use the following measure to meet your requirement:

B2B OUTLET BUYING =
SUMX (
    DISTINCT ( 'SUB TYPES'[Tag] ),
    CALCULATE (
        DISTINCTCOUNT ( [Outlet Name] ),
        'TOTAL SALES DATA'[Billing Type] = "ZRSS"
    )
)

Best regards,

[Anonymous]

Hi @v-lid-msft ,

Noted on the deletion of link.

And thank you! The formula works. Thank you very much

Rey

[Anonymous]

Try this NewMeasure = SUMX(SUMMARIZE('TOTAL SALES DATA','SUB TYPES'[Tag],"total",[B2B OUTLET BUYING]),[total])

The issue you faced because of incorrect parenthesis.

Thanks
Pravin

[Anonymous]

Try the measure which i have corrected.

[Anonymous]

Try this
Sumx(summerize(table,table[column]"total",[b2b outlet]),[total])

Thanks,
Pravin

[Anonymous]

Hi, I'm sorry, I'm very new at Power Bi.

Can I send the link of my dashboard instead?

[Anonymous]

Go to power bi desktop

Click on home then click on meausre
Write the above dax
And add that measure and required column to table visual.


Note :change the column and table names as per requirement.

[Anonymous]

NewMeasure = SUMX(SUMMARIZE('TOTAL SALES DATA','SUB TYPES'[Tag],"total",[B2B OUTLET BUYING],[total]))

result was "Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2."