Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I am trying to solve this probably simple problem but came up with strange result. I have a tLevels table with different grouping levels like this:
Lev1 Lev2 ProductID
1 12 A
1 12 B
1 4321 C
2 133 D
2 12 E
then I have a sales tSales table:
ProductID Amount
A 100
A 10
B 200
B 150
C 80
D 300
E 260
The tables are related via ProductID. I was expecting to sum ProductIDs by Lev1 with
SumAmount = CALCULATE(SUM(tSales[Amount]),tLevels[Lev1])
However I get this strange result
Any ideas why is it not summing by Lev1 giving me the same result on all lines?
Solved! Go to Solution.
Well, it works well for me based on the sample data you provided.
You can check the desktop file I created with your data and compare to yours.
https://superfarb.com/wp-content/uploads/2017/07/Levels.pbix_.zip
Ziv Himmelfarb
If you want to show Amount by Lev1,
the measure will simple be =SUM(Amount).
You don't even have to create a measure for it, just create a visual with tLevels[Lev1] and tSales[Amount]
and make sure the aggregation used for tSales[Amount] is SUM.
The problem with the measure you've created is that CALCULATE changes the context filter for the fields you are filtering by
(in this case tLevels[Lev1]). The second argument to CALCULATE is a flter. You just used tLevels[Lev1] as a filter,
which always returns TRUE, therefore all values are the same.
I hope that helps.
Ziv Himmelfarb
Thanks for the comments. I actually tried to put up a table visual without any measures before posting this question. It returns even more strange result:
My table relationship looks like:
It doesn't help if Cross filter direction is Both or Single.
Any page level or report level filters in use?
No.
Well, it works well for me based on the sample data you provided.
You can check the desktop file I created with your data and compare to yours.
https://superfarb.com/wp-content/uploads/2017/07/Levels.pbix_.zip
Ziv Himmelfarb
Your example works fine. I investigated thoroughly my data set and found out that product A had some odd non-printables in tSales.
Thanks!
Anytime you see a result like this (all values returned are the same) it usually means that your table relationships are not set correctly between your tLevels and tSales tables. Try looking at the relationship flow between your tables and select the other choice (one or both).
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |