Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi.
A rookie question I hope someone can help me with.
I have 2 tables. 1 tables with categories and another table with same categories, but also with a number.
So, what I want to do is calculate how many of the categories in table 1 based on the number with same category in table 2.
Please look at my attach example of tables at the bottom.
As you can see in my example tables at the bottom I have Apples 3 times in table 1, and in table 2 an apple have the number 2. Then I need to multiply 3x2=6. Bananas is also 3 times, but the number there is 4, so then I need to multiply 3x4.
This I want to do with a measure and then summarize it all up in one KPI vizual.
Anyone who can help me with this?
Best regards
Marius
Solved! Go to Solution.
Hey,
you can use DAX statement to create a measure in Table 2
Measure = SUMX( 'Table2' ,var currentCategory = 'Table2'[Category] return 'Table2'[Amount] * COUNTROWS( FILTER('Table1' ,'Table1'[Category] = currentCategory ) ) )
Hope this is what you are looking for
Regards and have a nice weekend
Tom
Hi,
From Table2, drag Ctegory to the visual. Enter this calculated field formula
=SUMX(Table2,Table2[Number]*COUNTROWS(Table1))
Hope thish helps.
Hey,
you can use DAX statement to create a measure in Table 2
Measure = SUMX( 'Table2' ,var currentCategory = 'Table2'[Category] return 'Table2'[Amount] * COUNTROWS( FILTER('Table1' ,'Table1'[Category] = currentCategory ) ) )
Hope this is what you are looking for
Regards and have a nice weekend
Tom
Superb, exactly what I needed! Thank you so much.
Best regards
Marius
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |