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

[krichmond]

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

[tamerj1 · Accepted solution]

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.

[krichmond]

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

@krichmond 

Please calrify further perhaps with example. Thank you

[krichmond]

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

[tamerj1]

@krichmond 

What type of aggregation do the columns have? SUM?

[krichmond]

@tamerj1 - Yes, it is SUM.

[tamerj1]

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]

[krichmond]

@tamerj1 - I am getting the following error message.

[tamerj1]

@krichmond 

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

[krichmond]

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

[tamerj1]

@krichmond 

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

[krichmond]

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

[tamerj1]

Use [Quantit]

[tamerj1]

@krichmond 

Total Quantity = SUM ( Sheet1[Quantity] )