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.
measures:
what I would like to achieve:
First, I tried summarizecolumns, but it gives "SummarizeColumns() and AddMissingItems() may not be used in this context" error message when put together with the dimentions in a table visual.
Then I created a table that works, but when I used it as a measure (by encapsulating the code below in a SUMX function), the measure did not create the correct values. Also, the crossjoin is a really heavy function to use. There must be a better way to do this. Does anyone have a suggestion on how to make this the right way?
since I cannot share the pbix file (such a shame) here is the data:
Fact1:
fact1_id;dim10_id;amount
1;1;1
2;2;2
3;2;1,5
Fact2:
fact2_id;dim10_id_f;dim20_id;amount
1;1;3;2
2;1;4;2
3;2;3;1
4;2;4;2
Dim10
dim10_id;dim10_name
1;name1
2;name2
Dim20
dim20_id;dim20_name
3;name3
4;name4
Solved! Go to Solution.
Create a couple of measures,
Measure 1 =
VAR SummaryTable =
ADDCOLUMNS (
VALUES ( Dim10[dim10_name] ),
"@val",
VAR SumAll =
CALCULATE ( [SumAll], REMOVEFILTERS ( Dim20 ) )
VAR Fact2Sum = [Fact2Sum]
RETURN
SumAll * Fact2Sum
)
RETURN
SUMX ( SummaryTable, [@val] )
I'm not too sure what you're definition of
"I want to exclude 'dim20' from the 'SumAll' calculation",
but see if this is what you are after (I'm posting all the measures so you can correct if a step is the wrong calculation):
Sum f1 =
SUM('fact 1'[amount])
Sum f2 =
SUM('fact 2'[amount])
Exclude dim20 =
CALCULATE([Sum f2], ALL(dim20))
SumALL =
[Sum f1] - [Exclude dim20]
Final Measure =
SUMX(dim10, [SumALL] * [Sum f2])
To get:
Proud to be a Super User!
Paul on Linkedin.
Thank you @johnt75 and @PaulDBrown. Both of your comments corrected my mistake and helped me solve my issue.
I used ALL(Fact2[dim20_id]) instead of ALL(dim20)
I'm not too sure what you're definition of
"I want to exclude 'dim20' from the 'SumAll' calculation",
but see if this is what you are after (I'm posting all the measures so you can correct if a step is the wrong calculation):
Sum f1 =
SUM('fact 1'[amount])
Sum f2 =
SUM('fact 2'[amount])
Exclude dim20 =
CALCULATE([Sum f2], ALL(dim20))
SumALL =
[Sum f1] - [Exclude dim20]
Final Measure =
SUMX(dim10, [SumALL] * [Sum f2])
To get:
Proud to be a Super User!
Paul on Linkedin.
Create a couple of measures,
Measure 1 =
VAR SummaryTable =
ADDCOLUMNS (
VALUES ( Dim10[dim10_name] ),
"@val",
VAR SumAll =
CALCULATE ( [SumAll], REMOVEFILTERS ( Dim20 ) )
VAR Fact2Sum = [Fact2Sum]
RETURN
SumAll * Fact2Sum
)
RETURN
SUMX ( SummaryTable, [@val] )
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |