Forum Discussion
How to sum table1 based on table2 values
Hello Guys,
I have two tables:
| Dimesion Table | Fact Table | ||||||
| Name | Price | Name | Date | ||||
| Doc1 | 10 | Doc1 | 01/06/2020 | ||||
| Doc2 | 20 | Doc1 | 02/06/2020 | ||||
| Doc3 | 30 | Doc1 | 03/06/2020 | ||||
| Doc4 | 40 | Doc2 | 04/06/2020 | ||||
| Doc5 | 50 | Doc2 | 05/06/2020 | ||||
| Doc2 | 06/06/2020 |
My objective is:
| Sum | |
| Name (Fact Table) | Price (Dimension Table) |
| Doc1 | 30 |
| Doc2 | 60 |
But when i sum the price, result is 150.
How can i sum only ''Names'' in Fact Table?
Hi,
Create a relationship from the Name column of the Fact Table to the Name column of the Dim Table. In the Fact Table, write this calculated column formula
Prices = related('Dimension table'[Price])
To your visual, drag Name from the Dimension Table and write this measure
Total = SUM('Dimension table'[Prices])
Hope this helps.
2 Replies
Hi,
Create a relationship from the Name column of the Fact Table to the Name column of the Dim Table. In the Fact Table, write this calculated column formula
Prices = related('Dimension table'[Price])
To your visual, drag Name from the Dimension Table and write this measure
Total = SUM('Dimension table'[Prices])
Hope this helps.