sumif not working

[homboy27]

Hello - I would like to add in a sumifs type of formula in Table B. I would like to add in sales by total client and by date from Table A in Table B. What is the formula I can use for that?

[MarkLaf]

I assume you are looking for a solution in Power Query, applying the transformation before you load the tables into your model.

Table A (same as provided in snip)

Client	Date	Sales
Client a	3/8/2025	20
Client a	3/8/2025	40
Client a	4/5/2025	24
Client a	4/12/2025	35
Client b	3/8/2025	22
Client b	4/5/2025	50

Table B (same as provided, plus a client/date, [client c, 3/8/2025], that does not exist in Table A to show how that scenario is handled)

Client	Date
Client a	3/8/2025
Client a	4/5/2025
Client a	4/12/2025
Client b	3/8/2025
Client b	4/5/2025
Client c	3/8/2025

The following two steps (MergeTableA, SumTableASales) pasted into advanced editor will get what you want:

let

    <...>

    PreviousStep = <...>,

    MergeTableA = 
    Table.NestedJoin(
        PreviousStep, {"Client", "Date"}, //change "PreviousStep" to actual step name before this
        #"Table A", {"Client", "Date"}, 
        "Sales", JoinKind.LeftOuter
    ),
    
    SumTableASales = 
    Table.TransformColumns( 
        MergeTableA, 
        { "Sales", each List.Sum( [Sales] )??0, type number } 
    )

in
    SumTableASales

Output:

[v-pagayam-msft · Accepted solution]

Hi @homboy27 ,
Thank you for reaching out to us on Microsoft Fabric Community Forum!

Try using below DAX:

SalesByClientAndDate =
CALCULATE(
SUM(TableA[SalesAmount]), -- Replace with the actual column
TableA[Client] = TableB[Client],
TableA[Date] = TableB[Date] 
)

Please refer the solved links below:
Solved: Sumifs in power bi - Microsoft Fabric Community
Solved: Power BI formule Sumifs - Microsoft Fabric Community

Hope this helps.If so, consider accept it as solution.

[homboy27]

That does not work, any other solutions?

[homboy27]

I dont need to join the tabes. I just need help with the sumifs formula. 

[lbendlin]

I hope someone else can help you further with that.

[lbendlin]

You don't really need to do that - the Power BI visuals will do the grouping for you.

But if you must = in Power Query there is Table.Group()

[homboy27]

I have 2 queries. I need to add in the sumifs formula so it comes through in the Table 2 (2nd query)

[lbendlin]

Join the tables in the data model. Let the data model do the work for you.