Forum Discussion
Helper IDAX: Calculating sum over relative table
Helper II solved this by using SQL but I would still like to know how to do this in DAX.
Solution was something like
SELECT C.AId, C.BId, SUM(number) OVER (PARTITION BY AId)
FROM C
WHERE AId IS NOT NULL AND BId IS NOT NULL
GROUP BY AId, BId
- MattAllington
Community Champion
It's a bit hard to understand what you want. How about you build a small sample workbook, make the joins and show the output you are after
- Z7-852
Table A
ID 1 2 3 4 5 Table B
ID 1 2 3 4 5 Table C (Consumption number of B by user A. Also contains data like date)
Almost all the information needed is in table C.
AID BID NUMBER 1 1 10 3 1 20 3 2 30 4 1 40 3 2 50 Result
AID BID SUM(number) 1 1 10 3 1 20 3 2 80 4 1 40 The SQL code that I posted worked and I was also able to use SQL to calculate other totals like (B1 usage in this example 70 and total A3 usage in this example 100).
Problem with SQL code that I used was that it removed time dimension from data because of the GROUP BY clause.
It would be nice if I had one measure that would tell me "Does this A consume more B than avarage A?"- sdjensen
Solution Sage
If I understand you correct all you need is to load all 3 table into PBI Desktop and create relationships from c to a and c to b.
Create a measure that sum your number from c.
Finally add Id from a and b and the new measure in a matrix visual.
