Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a calculated measure with formula as measure 1 = sum(a)/sum(b) now I want to create another measure which will do = sum of all(measure1)
Dim A B Measure1 Measure2
A 10 5 2 11
B 100 25 4 11
C 1000 20 5 11
Current behaviour of measure2 is sum of all A / sum of all B = 22.2, I do not want this. How can I calculate sum of measure1 across all rows?
Please help
Thanks in advance
Solved! Go to Solution.
Hi @2366 ,
We can try to use the following measure to meet your requirement:
TotalMeasure =
SUMX (
DISTINCT ( 'Table'[Year] ),
CALCULATE (
[First Measure]
)
)
It if does not meet your requirement, could you please share your 1st measure if it does not contain any confidential information?
Best regards,
So, you want to create a table variable VAR and use ADDCOLUMNS, something like:
Measure2 =
VAR __Table =
ADDCOLUMNS(
'Table',
"Measure1",[Measure1]
)
RETURN
SUMX(__Table,[Measure1])
Thanks but the new measure is now returning blanks.
Aplogies I havent given the full background of what I am trying to achieve -
I have a data set like this
Year Month 1 2 3 4
1 100 300 600 1500
2 200 400 800
3 300 500
4 400
I am calculating my 1st measure as sum(next months value)/sum(current months value) by raking the month column.
so my result currently looks like this now -
Year M 1 M2 M3
1 3 2 .4
2 2 2
3 .6
In the next step I want to display a single row as
M1 M2 M3
5.6 4 .4
Hope this helps?
Hi @2366 ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Hi @2366 ,
We can try to use the following measure to meet your requirement:
TotalMeasure =
SUMX (
DISTINCT ( 'Table'[Year] ),
CALCULATE (
[First Measure]
)
)
It if does not meet your requirement, could you please share your 1st measure if it does not contain any confidential information?
Best regards,