cancel
Showing results for
Did you mean:  Helper III

## Need help with a calculated field that takes the variance for each row.

Need help with a calculated field that takes the variance for each row. I would need the variance from the "Event Set Up Date" to the "End of Mailing Month" and then the variance from the "End of Mailing Month" to the "All Drops Mature Date". 1 ACCEPTED SOLUTION  Super User

Hi @krichmond
First of all please accept my apology for causing such confusion. I admit that I did not pay enough attention to the details therefore unintentionally misleading you and waisting your time.

Here is the solution:

You are using a matrix visual where you're having the [Production Stage] column at the columns of your matrix. While you have only one value which is the quantity. If you wish to add the two variances to the matrix it will not look as you wish because this will triple the total columns of the matrix from 3 to 9. Therefore, I would recommend to use a table visual instead while you keep the [Client ID] and the [Client Name] on the values (acting as rows). On the other hand as the [Production Stage] column has only three distinct values, we can create a measure representing each of these values as follows:

``````Event Set Up Date Quantity =
CALCULATE (
SUM ( Sheet1[Quantity] ),
Sheet1[Production Stage] = "Event Set Up Date"
)``````
``````End of Mailing Month Quantity =
CALCULATE (
SUM ( Sheet1[Quantity] ),
Sheet1[Production Stage] = "End of Mailing Month"
)``````
``````All Drops Mature Date Quantity =
CALCULATE (
SUM ( Sheet1[Quantity] ),
Sheet1[Production Stage] = "All Drops Mature Date"
)``````

Now you can create the variance measures as follows:

``Variance 1 = [Event Set Up Date Quantity] - [End of Mailing Month Quantity]``
``Variance 2 = [End of Mailing Month Quantity] - [All Drops Mature Date Quantity]``

Please let me know if you need any help. You can also share a sample file and I can do it for you.
Thank you for your patience and have a good day.

14 REPLIES 14  Super User

Hi @krichmond
First of all please accept my apology for causing such confusion. I admit that I did not pay enough attention to the details therefore unintentionally misleading you and waisting your time.

Here is the solution:

You are using a matrix visual where you're having the [Production Stage] column at the columns of your matrix. While you have only one value which is the quantity. If you wish to add the two variances to the matrix it will not look as you wish because this will triple the total columns of the matrix from 3 to 9. Therefore, I would recommend to use a table visual instead while you keep the [Client ID] and the [Client Name] on the values (acting as rows). On the other hand as the [Production Stage] column has only three distinct values, we can create a measure representing each of these values as follows:

``````Event Set Up Date Quantity =
CALCULATE (
SUM ( Sheet1[Quantity] ),
Sheet1[Production Stage] = "Event Set Up Date"
)``````
``````End of Mailing Month Quantity =
CALCULATE (
SUM ( Sheet1[Quantity] ),
Sheet1[Production Stage] = "End of Mailing Month"
)``````
``````All Drops Mature Date Quantity =
CALCULATE (
SUM ( Sheet1[Quantity] ),
Sheet1[Production Stage] = "All Drops Mature Date"
)``````

Now you can create the variance measures as follows:

``Variance 1 = [Event Set Up Date Quantity] - [End of Mailing Month Quantity]``
``Variance 2 = [End of Mailing Month Quantity] - [All Drops Mature Date Quantity]``

Please let me know if you need any help. You can also share a sample file and I can do it for you.
Thank you for your patience and have a good day.  Helper III

@tamerj1 - No worries! Let me just say this... I have never received such amazing support in my life on a community forum. I cannot thank you enough. We recently switched from Tableau to Power BI and I am at the very beginning of the learning curve. This worked perfectly. I hope you have a fantastic day.  Super User

Please calrify further perhaps with example. Thank you  Helper III

@tamerj1 - No problem. So for the row with 101000052, the variance between the "Event Set Up Date" to the "End of Mailing Month" would be 3,905 and the variance between the "End of Mailing Month" to the "All Drops Mature Date" would be 75.  Super User

What type of aggregation do the columns have? SUM?  Helper III

@tamerj1 - Yes, it is SUM.  Super User

Then I would prefer to add them as Measures. Example:

Event Set Up_Date = SUM ( Table[Event Set Up Date] )

End of Mailing_Month = SUM ( Table[End of Mailing Month] )
All Drops Mature_Date = SUM ( Table[All Drops Mature Date] )

After that you can perform simple subtraction:

[Event Set Up_Date] - [End of Mailing_Month]
or
[End of Mailing_Month] - [All Drops Mature_Date]  Helper III

@tamerj1 - I am getting the following error message.   Super User

Please use your table name. If it is Table then write 'Table'  Helper III

@tamerj1 - Tried that and it still isn't working. I am new to Power BI (obviously), so apologize for all of the issues with this. Is there a way to post the work file so you can get in there and take a look? I tried but it wouldn't let me attached a file.   Super User

No worr Sometimes you have hidden characters. Better to copy the column name from the table header and paste it  Helper III

@tamerj1 - I don't see a column heading named "Event Set Up Date" but I do see that in your formula that you provided. Maybe that is what is causing the issue?  Super User

Use [Quantit]  Super User

Total Quantity = SUM ( Sheet1[Quantity] ) Announcements #### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023. #### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers! #### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture. Top Solution Authors
Top Kudoed Authors
Users online (4,673)