Forum Discussion
Helper IDAX: Calculating sum over relative table
Helper ITable 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?"
Solution SageIf 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.
- Z7-852
Matrix visual could be an easy answer if there weren’t over 2 million rows in both A and B. Also you don’t have any interaction in the matrix.
Ideal case would be if I could have to bar charts that are both using the SUM(C[Number]) as values and A and B respectably as rows. Both A and B have multiple “upper class” which I can use to drill down.
By selecting one row from A column the measure would answer “Is this A using all the Bs more or less than average A” and the B columns would answer “Compered to average how much more or less is A consuming this B?”
And when selecting column from B measure would answer “Is this B used more by all the As relative to average B” and A columns would answer “Compered to average how much B is consumed by this A?”
The measure should be something like SUM(visually filtered) / SUM(all).
I have managed to do this using SQL GROUP clause. But the problem is I cannot compere time periods. For example if some of the B:s have been replaced with other items.
- sdjensen
What I meant with my answer was that you don't need any cross join, group by or any fancy SQL to do what you described all this will happen correctly if you have the right relationships in you model and create the right calculations, but it's really hard to come closer to a result with the info provided.
The Matrix was just an example to return the result as you described it almost any visual will be able to do the same. Like if you have a visual with aID and then bID in a hierarchy you can drill down from a member of a to see all the bID's that has values for this aID.
I don't think you will find any visual that will give you a nice view of 2 x 2,000,000 members and present in on a regular size monitor. You would have to filter these first before making any sence.