How to create combination of static table and measures?

[Anonymous]

If I would like to have a table of the following format:

Values	Sales(avg)	Cost(avg)	Profit
Plan
Actual
Difference

Where profit is simply [Sales(avg)]-[Cost(avg)], Sales(avg) is a measure of the average sales over a defined a timed period (e.g. selected by a slicer). Similar for Cost(avg).

How is this possible to do whilst keeping the leftmost column static? Any help would be much appreciated, thank you.

[Anonymous]

HI @Anonymous,

Measure calculation results are based on its row contents, they will be changed if you do any filter or change category that affects current row contents.

So if you want to keep static results on the measure, please modify your filter conditions with accurate and static values instead of dynamic value based on the current row contents.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous ,

Thank you for your response. The only section that I would like to keep static is the leftmost column. The measures should change dynamically based on some filter condition. They would be measures because they take an average over a predefined time frame. However, it is this mixing of the static left hand column with dynamic measures that I am unsure how to achieve.

[Anonymous]

Hi @Anonymous,

The filter effect of slicer and 'different level' filter not really affect the data mode table records. Their source data tables are virtual tables generated from data model tables. You can't use child level to effect its parent.

Spoiler

Notice: different data levels in power bi.

query editor(queries and query parameters) -> data model(tables, calculate table/column) -> data view(measure, visuals, filters)

For this scenario, I'd like to suggest you try to use query parameters to effect generated data model tables to achieve dynamic tables.

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous 

does the above reply helps. if you need more help make me @

Appreciate your Kudos.

[Anonymous]

Hi @amitchandak , thank you I will look at it and I have downloaded it so you can stop the share.

[Anonymous]

Hi - 

Sure, will remove the share. Btw the share was done by me and not by amit. If the solution worked for you, please consider selecting my response as the answer.

Regards

E

[Anonymous]

Hi @Anonymous , apologies. Sadly the share wasn't exactly what I was looking for as your sales and cost columns aren't measures, they are hard coded, static values which is not what I am looking for. But thank you!

[Anonymous]

Hi there - 

I tried something like below... Is this what you were looking for? (please note that I reversed the formula for the Profit though)

I apologize for that... Please see the below link:

https://drive.google.com/file/d/1gjSdwBoiSKb0EZjWR1mTq5tpLzC6a_u3/view?usp=sharing

Please confirm once you download the file, I will remove the share after that.

Regards

E

[Anonymous]

Hi @Anonymous , I can't see anything below 🙂