Need Help with Creating/Adding a calculated field/column and displaying

[Anonymous]

Hello PowerBI community!

Starting to get my feet wet with PowerBI and want to continue to learn. 

I'm struggling to find the easiest/most useful way to created a calculated field/column in Power BI.

I have $ projections from the demand planners and account teams every period that I want to manipulate in PowerBI. 

Below is a small sample of the data (full set is 10000 rows)

I want to be able to add a field that shows the difference between the demand and account team $ projects by $ and by %. I also want to be able to aggregate the accounts that fall in the West and show their $ and % variance. ( I can group on my own)

Is there an easy way to do this in the Data and Report tabs?

I really appreciate any help!

[Anonymous]

Hi @Anonymous ，

What's your expected results? Could you please provide some samples? If the following screenshot is what you want to achieve?

Best Regards

Rena

[Anonymous]

Hello @Anonymous,

Thanks in advance for the help!

I would love to have a view like the one you have below, but I would also want to look at the variance ($ and %) for the full year by account ( so all my Periods aggregated) and also the variance ($ and %) by Period and by Account. 

Also beyond that, would it be possible to add in calculated rows in the "source column" that would be named "Demand-Acct in $" and "Demand-Acct %" that show the variances in the "value" column?

I really appreciate the help!

[Anonymous · Accepted solution]

Hi @Anonymous ，

I created one sample PBIX file based on your needs, you can find the details in this file. Regarding to the second point you mentioned, I am not very clear. Could you please provide an example?

"Also beyond that, would it be possible to add in calculated rows in the "source column" that would be named "Demand-Acct in $" and "Demand-Acct %" that show the variances in the "value" column?"

Best Regards

Rena

[Anonymous]

Thank you @Anonymous, this was incredibly helpful!

[Anonymous]

@Anonymous I hate to be a bother, but is there anyway to use those same measures as calculated columns so that I can use them as filters for my graphs?

So I can display the bars and or lines as "Demand - Account" or "Demand vs Account % Variance" by period and in total for the full year?

Sorry for asking another question. Just trying to learn!

[amitchandak]

Try like

demand diff = calculate(sum(table[value]),table[source]="Account") - calculate(sum(table[value]),table[source]="Demand")

Ratio = divide(calculate(sum(table[value]),table[source]="Account") ,calculate(sum(table[value]),table[source]="Demand"))

For Others please provide sample data and expected output

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

Thanks for the quick response!

So when I enter in your top formula it doesn't subtract the 2 fields... 

I very well may be entering it wrong, but the formula is returning a negative Account number (just changing it to a negative value of the same number), and the Demand number is just the same number in the value column.

Would you know of where I possible could have caused an error?