cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
krichmond
Helper III
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".

 

Screenshot For Reddit Post.PNG

1 ACCEPTED SOLUTION
tamerj1
Super User
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.

View solution in original post

14 REPLIES 14
tamerj1
Super User
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.

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

tamerj1
Super User
Super User

@krichmond 

Please calrify further perhaps with example. Thank you

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

@krichmond 

What type of aggregation do the columns have? SUM?

@tamerj1 - Yes, it is SUM.

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] )
Then add the measures to the instead of the columns.


After that you can perform simple subtraction:


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

@tamerj1 - I am getting the following error message.

 

Screenshot For Reddit Post 1.PNG

@krichmond 

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

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

 

Screenshot For Reddit Post 1.PNG

@krichmond 

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

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

Use [Quantit]

@krichmond 

Total Quantity = SUM ( Sheet1[Quantity] )

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

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