Need help with a Power BI Report with dynamic calculations on columns

[Anonymous]

Hi!

A collegue of mine gave me a very special report requirement in Power BI that I am very uncertain about can be developed. But I hope some of you in the community has knowlegde how to do this. Below is a picture that shows the outcome of the report.

This report is a Power BI matrix visual and consists of a number of measures which are placed on rows and two columns chosen from the Version table (dimension) . Versions are shown to the right only just to show you that two versions are selected and dragged into the matrix visual. The special thing in this report is that the Difference between two selected Versions must be calcutated on the fly. We can drag two other versions into the visual and new values will be calculated on the Difference column.

The question is how can I develop such a report that will function as described. I really would like any suggestion or example in Power BI if you can

Regards Geir

[v-lili6-msft]

hi  @Anonymous 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, for your case, you could try my this sample pbix file.

Regards,

Lin

[Anonymous]

Hi again. Been away for a while. This seems promising. I will take a sharper look at you code to match my case. Again thanks regards geir

[PaulDBrown]

@Anonymous 

If I understand your predicament, you can (so to speak) do what you are asking, but...there are quite a few "but"s...

If you want your users to freely drag "versions" onto the table, they will also need to know which measures (to calculate the differences) to drag onto the matrix. In other words, very complex/convoluted and not at all user firendly.

The best solution I can suggest is to create however matrices (with the possible combinations) needed and then let your users choose the outlook they wish to see using bookmarks and buttons.

Does that make sense?

[Anonymous]

Hi again and thanks for fast respond.

Perhaps a solution could be having spesial versions that consist of latest budget so that version will be calculated already. If I can start with a simpler solution may be that will be enough. So the report layout will be the same but [latest budget] will be recalculated next time a new budget will be in place.