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 everyone.
I have "net shipmant" column from 2019 and "net shipmnat" from 2018.
"variance order" is my 2019 and 2018 filter.
now I want to create 2 columns wich will be calculate
1. Net variance $ (2019-2018)
2. Net variance %
For the first case the results are :
$152.50 - $499.00 = ($346.50)
$0 - $118.48 = ($118.48)
$1,655.88 - $5,466.85 = ($3,810.97)
......
my sql code: =Sum(iif(Fields!VarianceOrder.Value=1,val(Fields!NetShipments.value),0))
-
Sum(iif(Fields!VarianceOrder.Value=2,val(Fields!NetShipments.value),0))
For the second case the results are:
-69.44%
-100.00%
-69.71%
.....
my sql code: =(Sum(iif(Fields!VarianceOrder.Value=1,val(Fields!NetShipments.value),0))
-
Sum(iif(Fields!VarianceOrder.Value=2,val(Fields!NetShipments.value),0)))
/
Sum(iif(Fields!VarianceOrder.Value=2,val(Fields!NetShipments.value),0))
Please help me solve this problem 🙂
Solved! Go to Solution.
Hi @Anonymous
You may create two measures to get the value.
Percentage = VAR _2019 = CALCULATE ( SUM ( Table1[NetShipments] ), FILTER ( Table1, Table1[Year] = 2019 ) ) VAR _2018 = CALCULATE ( SUM ( Table1[NetShipments] ), FILTER ( Table1, Table1[Year] = 2018 ) ) RETURN IF ( ISFILTERED ( Table1[Year] ), SUM ( Table1[NetShipments] ), FORMAT ( DIVIDE ( _2019 - _2018, _2018 ), "Percent" ) )
Regards,
Hi @Anonymous
You may create a measure like below.Attached sample file for your reference.
Measure = VAR _2019 = CALCULATE ( SUM ( Table1[NetShipments] ), FILTER ( Table1, Table1[Year] = 2019 ) ) VAR _2018 = CALCULATE ( SUM ( Table1[NetShipments] ), FILTER ( Table1, Table1[Year] = 2018 ) ) RETURN IF ( ISFILTERED ( Table1[Year] ), SUM ( Table1[NetShipments] ), IF ( SELECTEDVALUE ( Table2[Order] ) = 1, _2019 - _2018, IF ( SELECTEDVALUE ( Table2[Order] ) = 2, DIVIDE ( _2019 - _2018, _2018 ), SUM ( Table1[NetShipments] ) ) ) )
Regards,
it's not work 😕 ... are there any other idea?
@v-cherch-msft , I need two columns one is substraction another one is percentage. In your example I understand that with checkbox i can select wiche one i want to see, but checkbox in my report is just 2018 and 2019 years columns
Hi @Anonymous
You may create two measures to get the value.
Percentage = VAR _2019 = CALCULATE ( SUM ( Table1[NetShipments] ), FILTER ( Table1, Table1[Year] = 2019 ) ) VAR _2018 = CALCULATE ( SUM ( Table1[NetShipments] ), FILTER ( Table1, Table1[Year] = 2018 ) ) RETURN IF ( ISFILTERED ( Table1[Year] ), SUM ( Table1[NetShipments] ), FORMAT ( DIVIDE ( _2019 - _2018, _2018 ), "Percent" ) )
Regards,
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |