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 All,
I have calculated two measures gross margin and profit margin as above.
I have a dimension in my data set "actual" and "plan". I am able to place the actual and plan in the column but i am unsure of how to put a variance (actual - plan) into the column beside "plan". Is there a way to do this?
Thanks for your help
Solved! Go to Solution.
Hi @Anonymous ,
You need to create 3 measures as below:
Gross Margin = CALCULATE(MIN('Table'[Value]),ALLEXCEPT('Table','Table'[Category]))
Profit = CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Category]))
result =
VAR actual =
CALCULATE (
SWITCH (
SELECTEDVALUE ( 'Table (2)'[row] ),
"Gross Margin", 'Table'[Gross Margin],
"Profit", 'Table'[Profit]
),
'Table'[Category] = "actual"
)
VAR plan =
CALCULATE (
SWITCH (
SELECTEDVALUE ( 'Table (2)'[row] ),
"Gross Margin", 'Table'[Gross Margin],
"Profit", 'Table'[Profit]
),
'Table'[Category] = "plan"
)
RETURN
SWITCH (
SELECTEDVALUE ( 'Column'[Column] ),
"actual", actual,
"plan", plan,
plan - actual
)
and another 2 tables showed as below:
Finally you will see:
For the related .pbix file,pls click here.
Best Regards,
Kelly
How you create the current view. Can you share calculations.
So gross profit is calculated as profit/revenue and gross margin is cost/revenue. The version is a column in my dataset containing "actual" and "plan".
Hi @Anonymous ,
You need to create 3 measures as below:
Gross Margin = CALCULATE(MIN('Table'[Value]),ALLEXCEPT('Table','Table'[Category]))
Profit = CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Category]))
result =
VAR actual =
CALCULATE (
SWITCH (
SELECTEDVALUE ( 'Table (2)'[row] ),
"Gross Margin", 'Table'[Gross Margin],
"Profit", 'Table'[Profit]
),
'Table'[Category] = "actual"
)
VAR plan =
CALCULATE (
SWITCH (
SELECTEDVALUE ( 'Table (2)'[row] ),
"Gross Margin", 'Table'[Gross Margin],
"Profit", 'Table'[Profit]
),
'Table'[Category] = "plan"
)
RETURN
SWITCH (
SELECTEDVALUE ( 'Column'[Column] ),
"actual", actual,
"plan", plan,
plan - actual
)
and another 2 tables showed as below:
Finally you will see:
For the related .pbix file,pls click here.
Best Regards,
Kelly
This one is tricky. Create a diff column. for these you need to use actual and plan as filter.
The using summarize and union , you have to merge the current result with new columns.
Hi @Anonymous
You can create a measure Variance = [actual] - [plan]
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |