Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Total Column Sum is not equal

Hi everyone! I've been stuck with this, hope you can help me. Thanks! The Total Sum should be 475.        
Need Help
Show and Tell
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Share the formula you are using as of now.

Anonymous's avatar
Anonymous
Not applicable
6 years ago
B2B OUTLET BUYING = CALCULATE(DISTINCTCOUNT([Outlet Name]),'TOTAL SALES DATA'[Billing Type]="ZRSS") Here
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      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's avatar
        danextian
        Icon for Super User rankSuper User
        6 years ago

        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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Try this
    Sumx(summerize(table,table[column]"total",[b2b outlet]),[total])

    Thanks,
    Pravin
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

       

      Can I send the link of my dashboard instead?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        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.
  • dethompson97's avatar
    dethompson97
    Icon for Resolver III rankResolver III
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      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's avatar
        Anonymous
        Not applicable
        6 years ago
        Try the measure which i have corrected.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Here,

       

       

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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,