Forum Discussion

homboy27's avatar
homboy27
Helper III
1 year ago
Solved

Summing by Client

Please see below 2 tables. I would like to bring in total sales from column B into Column A. However I do not want the total sales to be repeated if a client in table A has multiple rows. Is it possible so that total sales only show up on 1 row of Client A and do not show up again on another row?

Under Client A, I only want total sales to be 10k on 1 of the rows and not all the rows that are for Client A. Can you some please help?

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-kathullac's avatar
    v-kathullac
    1 year ago

    Hi homboy27,

    Thank you for reaching out to Microsoft Fabric Community Forum.

     

    can you try with this calculated column and let us know if you face any issues.this will pull total sales per client from Table B into Table A, but only show it once per client, on a specific row.

    TotalSales_Once = 
VAR CurrentClient = TableA[Client]
VAR CurrentLocation = TableA[Location]
VAR IsFirstRow =
    TableA[Location] = 
        CALCULATE(
            MIN(TableA[Location]),
            FILTER(TableA, TableA[Client] = CurrentClient)
        )
VAR TotalSales =
    CALCULATE(
        SUM(TableB[Sales]),
        FILTER(TableB, TableB[Client] = CurrentClient)
    )
RETURN
    IF(IsFirstRow, TotalSales, 0)

     

    Regards,

    Chaithanya.

7 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    1 year ago

    homboy27 

    Having difficulty understanding your requirement, could you show the expected outcome, you may paste the expected result in your reply.


    • homboy27's avatar
      homboy27
      Helper III
      1 year ago

      Fowmy 

       

       

      Please see below the desired section. I am pulling the total sales from Table B into Table A. However  I only want the sales for client A to be on 1 column and not on all the columns. So i would like the number that I highlighted to be 0 and not pull in $10k. 

       

       

      • rajulshah's avatar
        rajulshah
        Resident Rockstar
        1 year ago

        Hello homboy27 ,

         

        so what I understand is there's no effect of Location in the calculation of Sales. Please correct me if I am not correct.

    • v-kathullac's avatar
      v-kathullac
      Community Support
      1 year ago

      Hi homboy27,

      Thank you for reaching out to Microsoft Fabric Community Forum.

       

      can you try with this calculated column and let us know if you face any issues.this will pull total sales per client from Table B into Table A, but only show it once per client, on a specific row.

      TotalSales_Once = 
VAR CurrentClient = TableA[Client]
VAR CurrentLocation = TableA[Location]
VAR IsFirstRow =
    TableA[Location] = 
        CALCULATE(
            MIN(TableA[Location]),
            FILTER(TableA, TableA[Client] = CurrentClient)
        )
VAR TotalSales =
    CALCULATE(
        SUM(TableB[Sales]),
        FILTER(TableB, TableB[Client] = CurrentClient)
    )
RETURN
    IF(IsFirstRow, TotalSales, 0)

       

      Regards,

      Chaithanya.

  • v-kathullac's avatar
    v-kathullac
    Community Support
    1 year ago

    Hi homboy27 ,

    we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

    If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

    Regards,

    Chaithanya.