Forum Discussion
Calculating Expected Values to be Found with multiplication between columns in different tables
Hello, community!
I`ve been facing an issue where I need to correlate 3 different tables and multiply a Count on the first table with the number of results expected.
Here`s the problem:
Table 1:
| SERIALNO |
| XXX1 |
| XXX2 |
| XXX3 |
Table 2:
| SERIALNO | PRODUCTNO | SIDE |
| XXX1 | YYY1 | A |
| XXX1 | YYY1 | B |
| XXX2 | YYY1 | A |
| XXX3 | YYY2 | A |
Table 3:
| PRODUCTNO | SIDE | QTTSIDES |
| YYY1 | A | 2 |
| YYY1 | B | 2 |
| YYY2 | A | 1 |
| YYY3 | A | 2 |
| YYY3 | B | 2 |
Table3[QTTSIDES] = CALCULATE(
COUNTROWS('Table3'),
ALLEXCEPT('Table3','Table3'[PRODUCTNO])
)
I need to calculate, with those tables, the expected quantity (count) of a serial on table 2 if it appeared on table 1.
In this example, the result must be 5, because it expects that every serial from ProductNo 'YYY1' should appear 2 times on table 2 because of it's 2 sides, and every serial from ProductNo 'YYY2' should appear 1 time on table 2. So we can see that serial 'XXX2' is missing one side.
The point is calculating distinct Serials from table 1 * SideQtt from table 2 and comparing with the expected result, being able to view expected serials to be found on table 2 and the actually found.
I need to calculate, with those tables, the expected quantity (count) of a serial on table 2 if it appeared on table 1.
In this example, the result must be 5, because it expects that every serial from ProductNo 'YYY1' should appear 2 times on table 2 because of it's 2 sides, and every serial from ProductNo 'YYY2' should appear 1 time on table 2. So we can see that serial 'XXX2' is missing one side.
The point is calculating distinct Serials from table 1 * SideQtt from table 2 and comparing with the expected result, being able to view expected serials to be found on table 2 and the actually found.
2 Replies
- lbendlin
Super User
Can you reformulate your scenario in terms of "dimension" and "fact" tables?
v-weiyan1-msftCommunity Support
Hi arieldossantos ,
As lbendlin replied, to allow us to better understand the problem and help you, can you share sample data and the results you are hoping for? Show it as a screenshot or excel. Or a sample pbix after removing sensitive data.
Best Regards,
Yulia Yan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
