The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am stuck trying to maintain the same measure total when removing a column from a table visual. Here is my current measure, which gives the correct result and the correct total:
BD SUM =
VAR A =
SUMMARIZE (
Sheet1,
Sheet1[brand],
"Avg",
(
AVERAGE ( Sheet1[income] ) * Income[Income Value]
)
+ (
AVERAGE ( Sheet1[education] ) * Education[Education Value]
)
+ (
AVERAGE ( Sheet1[age] ) * Age[Age Value]
)
+ (
AVERAGE ( Sheet1[ethnicity] ) * Ethnicity[Ethnicity Value]
)
+ (
AVERAGE ( Sheet1[house] ) * 'Household Size'[Household Size Value]
)
+ (
AVERAGE ( Sheet1[marital] ) * 'Marital Status'[Marital Status Value]
)
+ (
AVERAGE ( Sheet1[children] ) * 'Presence of Children'[Presence of Children Value]
)
)
RETURN
SUMX (
A,
[Avg]
)
When adding this measure to a table visual, it generates this result:
id | brand | BD SUM |
9007900349 | A | 2.45 |
9007900349 | B | 1.63 |
9007900349 | C | 2.4 |
9007900349 | D | 2.5 |
9007900349 | E | 1.88 |
The total of all 5 values is 10.86 which is correct, however, when I remove the 'brand' column from the table visual, it gives the wrong total. Any ideas how I can produce this result?
id | BD SUM |
9007900349 | 10.86 |
I have tried every iteration of ALL, ALLEXCEPT, and VALUES I can think of but nothing seems to work. Appreciate the help!
Hi @HelpImStuck sure.
It could be transformed into dimension using power query
Proud to be a Super User!
Hi @HelpImStuck your definition for measure include brand, this is reason you "have" to have brand .Rewrite measure, try directly without variable part.
Sheet1[brand],
Proud to be a Super User!
Hi @some_bih, the measure needs to calculate the sum of the averages by brand, which in this case is 10.86, but the visual needs to display that by ID. In my example there are 5 brands under ID 9007900349, and the the sum of the averages on those 5 brands is 10.86, so in a bar chart with ID as the y-axis, ID 9007900349 needs to have the value of 10.86. For some reason this does not calculate correctly unless I also include brand
Hi @HelpImStuck did you try to use dimension table not fact table?
Proud to be a Super User!
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 |
---|---|
58 | |
32 | |
20 | |
17 | |
15 |
User | Count |
---|---|
56 | |
31 | |
30 | |
24 | |
21 |