How do I calculate "calculated rows" in power BI

[BrunoBarbosa]

Hi all,

I have a dataset displaying the P&L for different periods/types.

The columns show the values for: Previous Year, Actuals, Forecast, Dec Forecast, etc.

Rows show the accounts: Sales, Margin, etc.

I am trying to display this information on a Matrix Visualization.

What we would like to do is for example to use as rows Sales, Margin and "create" a third row with a measure of the margin percentage for each one of the scenarios, which are displayed in columns. The idea is that this calculation would vary according to any filters that are applied.

Example:

Accounts   |      Previous Year      |          Actuals          |        Forecast       |    Dec Forecast

Sales                    100                                100                          100                     100

GM                        20                                  30                            40                       55

% GM                   20%                              30%                        40%                    55%

Does anyone have an idea how to do it or if it is possible to be done?

Thank you,

[bajimmy1983]

@BrunoBarbosa , How are you? Como é que vai? Heheh! I am from Brazil. Don't know if you are, too. 

Since last year I was trying to accomplish the same thing (I think), but I could not find this post before.

Today I got back to this issue (challenge) and saw this post with a way to do so. I have simulated a very small dataset and then tried to apply the suggestions, but in my case I did not create other tables. This is the reason I am posting my files here today!

I simply added new lines to my dataset (just to have this line) in order to be possible to use DAX. I advance that in case you cannot add physical lines in the original dataset, I think you could then create another dataset just with the lines (eg: Revenue / Billings, Compensation / Revenue as I created) and date columns and then append to original dataset using Power Query, for example. 

One Drive Link: https://1drv.ms/f/s!Aq2_y0xKTrAvgqQaf7PIGkZVQYLFwQ

If you have any problem with download, please let me know.

I hope this can help others, too. 

Thanks and cheers,

Jimmy

[v-ljerr-msft · Accepted solution]

Hi @BrunoBarbosa,

A normal solution is that you may need to create different measures for Previous Year Sales, Previous Year GM, Previous Year %GM, Actuals Sales, Actuals GM, etc.

And you also need to create a table with a column called "Accounts Type" contains values "Sales", "GM", "%GM", and create another table with a column called "periods/types" contains values Previous Year, Actuals, Forecast, Dec Forecast, etc.

Then, you can make use of IF and SWITCH Function (DAX) to create a new measure to choose what value should be return depend on current values of "periods/types" and "Accounts Type", and show it on the Value field of the Matrix visual, with "periods/types" column set on Row field, and "Accounts Type" column set on Column field.

It's a little complex, but it could be done.

Regards

[parry2k]

Here is one interesting link and can help you. It is not direct solution to your question but get you started

https://community.powerbi.com/t5/Desktop/Create-abberanding-total/td-p/115909