cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## 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 IV

@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 IV

@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 IV

@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 IV

@tamerj1 - I am getting the following error message.

Super User

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

Helper IV

@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 IV

@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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors