98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » On a Power BI Matrix , I would like to calculate variance by mon
On a Power BI Matrix , I would like to calculate variance by mon
Resolved · Low Priority · Version 365
Mon 14 Oct 2024, 14:11Delegate Tony said...
Tony has attended:
Power BI Modelling, Visualisation and Publishing course
On a Power BI Matrix , I would like to calculate variance by mon
for simplification of explanation, I have a table with 3 columns:- Activity/Job ID
Estimated Cost of Job (as at snapshot date)
Snapshot date
This is a table built up each week as a snapshot of data.
I visualise this is PowerBI in a Matrix. With Activity/Job as rows. Estimated cost as values. Snapshot date as columns.
But i cant find a way to show the variance (up or down) from snap shot to snapshot each week. This is because the snapshot date is from the same column.
I have tried using a Measure but it doesn't show variance, just zero =
Estimated Cost as at Snapshot Date variance per Snapshot =
VARX.P(
KEEPFILTERS(VALUES('Table1'[Snapshot])),
CALCULATE(SUM('Table1'[Estimated Cost as at Snapshot Date]))
)
Mon 14 Oct 2024, 16:25Trainer Jens said...
RE: On a Power BI Matrix , I would like to calculate variance by
Hi Tony,
Thank you for the forum question.
It sounds like you’re trying to calculate the week-over-week variance in estimated costs for each job in Power BI. To achieve this, you can create a measure that calculates the difference between the current week’s estimated cost and the previous week’s estimated cost. Here’s a step-by-step approach to help you set this up:
Create a Date Table: Ensure you have a date table in your model. This table should include all the dates in your snapshot data and be marked as a date table in Power BI.
Create Relationships: Make sure your snapshot table is related to the date table via the Snapshot date column.
Create Measures: You need to create two measures: one for the current week’s estimated cost and one for the previous week’s estimated cost. Then, you can create a measure to calculate the variance.
Here’s an example of how you can do this:
EstimatedCostCurrentWeek =
CALCULATE(
SUM('Table1'[Estimated Cost of Job]),
FILTER(
ALL('Table1'),
'Table1'[Snapshot date] = MAX('Table1'[Snapshot date])
)
)
EstimatedCostPreviousWeek =
CALCULATE(
SUM('Table1'[Estimated Cost of Job]),
FILTER(
ALL('Table1'),
'Table1'[Snapshot date] = MAX('Table1'[Snapshot date]) - 7
)
)
EstimatedCostVariance =
[EstimatedCostCurrentWeek] - [EstimatedCostPreviousWeek]
Add Measures to Matrix: Add the EstimatedCostVariance measure to your matrix visual to see the week-over-week variance.
This approach assumes that your snapshots are taken exactly one week apart. If your snapshots are not exactly one week apart, you might need to adjust the - 7 part in the EstimatedCostPreviousWeek measure to match the actual interval between snapshots.
Let me know if you need further assistance.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 15 Oct 2024, 08:02Delegate Tony said...
RE: On a Power BI Matrix , I would like to calculate variance by
Hi Jens,
Many thanks for the prompt reply.
I seem to get the total variance for all jobs in that week, not just the variance by job
Thank you
Wed 16 Oct 2024, 10:33Trainer Jens said...
RE: On a Power BI Matrix , I would like to calculate variance by
Hi Tony,
It sounds like the measure is aggregating the variance across all jobs instead of calculating it individually for each job. To address this, we need to ensure that the variance calculation is done at the job level. Here’s how you can adjust the measures:
Current Estimated Cost by Job:
Current Estimated Cost by Job =
SUM('JobsTable'[Estimated Cost of Job])
Previous Estimated Cost by Job:
Previous Estimated Cost by Job =
CALCULATE(
[Current Estimated Cost by Job],
DATEADD('Date'[Date], -7, DAY) // Adjust the period as needed
)
Variance by Job:
Variance by Job =
[Current Estimated Cost by Job] - [Previous Estimated Cost by Job]
Make sure to use these measures in your Matrix visual. Add Activity/Job ID to Rows, Snapshot date to Columns, and the Current Estimated Cost by Job and Variance by Job measures to Values.
This should calculate the variance for each job individually rather than aggregating it across all jobs.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 18 Oct 2024, 10:53Delegate Tony said...
RE: On a Power BI Matrix , I would like to calculate variance by
HI Jens,
Thank you. That has worked.
Obviously that is for the current period and one previous period.
I will try and work out how to do it for the 5 other historical periods, as our reporting goes back 6 months
Many thanks
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Scheduled Refresh and Power BI ServiceIf you're using Power BI Service, set up scheduled refresh for your datasets. This ensures that your reports are always up-to-date with the latest data. Understand the refresh limits imposed by Power BI Service and optimize your data model and queries to stay within those constraints. |
