Forum Discussion
sumif not working
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?
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.
8 Replies
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()
I dont need to join the tabes. I just need help with the sumifs formula.
I hope someone else can help you further with that.
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.That does not work, any other solutions?
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 SumTableASalesOutput:
