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.
Hello All,
I am trying to create a column which compares all values to Net Sales. Our company calculates Net Sales as Gross Sales - Returns and Discounts. The picture below is what I'm trying to achieve: Net Sales are 150 and I'm trying to compare every other total to that value.
I'm sure there is a simple way to do this but every way I have tired does not give me the results I want. I have created two measures, the first one calculates Net Sales:
The next calculates the posted amount over the Net Sales measure:
I have also tried a different version of the above formulas:
Below are the results I'm getting based off of the formulas above. As you can see it's not dividing by the total Net Sales and in the case of the lower rows, it's dividing by 0 because they aren't in the net sales calculation.
Any insight would be greatly appericated.
Thanks,
Calen
Solved! Go to Solution.
Hi @calen ,
You could use ALLSELECTED( 'Your table' ). Here is a measure for your reference.
Measure =
VAR a =
CALCULATE (
SUM ( 'Table'[Posted Amount] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Row No.] IN { 4010, 4020, 4030, 4035, 4040 }
)
)
VAR b =
ABS ( SELECTEDVALUE ( 'Table'[Posted Amount] ) ) / a
RETURN
b
Here is the result:
Hi @calen ,
You could use ALLSELECTED( 'Your table' ). Here is a measure for your reference.
Measure =
VAR a =
CALCULATE (
SUM ( 'Table'[Posted Amount] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Row No.] IN { 4010, 4020, 4030, 4035, 4040 }
)
)
VAR b =
ABS ( SELECTEDVALUE ( 'Table'[Posted Amount] ) ) / a
RETURN
b
Here is the result:
Thank you for your help!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |