Summing by Client

[homboy27]

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?

[v-kathullac]

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.

[homboy27]

Can someone please help?

[v-kathullac · Accepted solution]

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.

[Fowmy]

@homboy27 

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

[homboy27]

@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. 

[Ashish_Mathur]

Hi,

In the first place, why do you want to do this?  Why not get 5,000 and 5,000?  Also, even if you want 10,000, why should it be for Location A, why not B?  Would you be OK with a Power Query solution?  If yes, then share data in a format that can be pasted in an MS Excel file.

[rajulshah]

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.