Dynamic column in dashboard that would recalculate based on parameter value?

[改善]

A very similar question to this one, but the answer in that thread was not working for me.

https://community.fabric.microsoft.com/t5/Power-Query/What-if-Parameter-for-Only-Selected-Values/td-...

Assume a table of the following structure: 

Brand	Product	Area	Cost 2023	Cost 2024	Required Input
Clock	SKU 400	UK	20	24	Batteries
Clock	SKU 400	UK	48	50	Digital Display
Clock	SKU 400	UK	5	9	Plastic Casing
Camera	SKU 20	China	3	5	Sensor
Camera	SKU 20	China	20	18	Processor
Camera	SKU 20	China	9	8	Plastic Casing

I want to build a dashboard which would have a table of the following format:

Q1. Is it possible to build something where the scenario cost 2024 would be a dynamic column, that would recalculate only when one of the required inputs in the process is selected in the graph in the right hand. ( it doesnt have to be a graph it can be also a dropdown list ) right now it calculates the new cost on all lines. 

Q2 And also take it to the next level, have multiple parameters, with custom appliance? i.e. 1 parameters adjusting volumes, 1 parameter adjusting price of X input, another paratmer adjusting price of Y input ( i assume it is possible)

Thanks to everyone that has read this far, thanks in advance for your help too! 

[改善]

Hi @v-jianpeng-msft thank you for the solution can u help me further please? I believe i have explained it poorly the first time. 

Please see the below screenshot, right now my bar chart selection does not interact with the Matrix. 

The expectation would be that i would select one required input in the bar chart, but my matrix would still show the full overview, without filtering on that requried input but recalculating only that input's value and the rest remain the same. 

In hindsight i probably need a DAX formula that would check IF an input is selected, then for that selected input multiply by (100 + Parameter Value ) otherwise all other inputs equal Cost 2024. 

So how do i make it so that the matrix on the left side show the whole data set, while only amending the inputs that are selected in the bar chart by the paramter? 

I hope i have not confused you even more

[v-jianpeng-msft]

Hi, @改善 

Thank you very much for your reply. Leaving the other two columns untouched and not filtered by other charts, I created the following DAX expression:

Sum of Cost 2023 =
CALCULATE ( SUM ( 'Table'[Cost 2023] ), ALL ( 'Table 2'[Required Input] ) )

Sum of cost 2024 =
CALCULATE ( SUM ( 'Table'[Cost 2024] ), ALL ( 'Table 2'[Required Input] ) )

Use the ALL function to clear the external filter. I rewrote the measure Scenario 2024:

Scenario 2024 =
IF (
    ISBLANK ( SELECTEDVALUE ( 'Table 2'[Required Input] ) ),
    CALCULATE (
        MAX ( 'Table'[Cost 2024] ) * ( 1 + [Parameter Value] ),
        FILTER ( 'Table', 'Table'[Required Input] = "Sensor" )
    ),
    IF (
        [Sum of Cost 2023] = BLANK ()
            || [Sum of cost 2024] = BLANK (),
        " ",
        CALCULATE ( MAX ( 'Table'[Cost 2024] ) * ( 1 + [Parameter Value] ) )
    )
)

If no other charts are selected by default, one of the inputs is calculated. When the corresponding input is selected in another chart, Scenario 2024 will also be recalculated. The results are shown in the image below:

If it is not checked, there will be a default output:

When one of the inputs is selected, it is recalculated:

I've provided the PBIX file used this time below. 

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[改善]

Hi Jianpeng,

Thank you for the solution, this is somewhat in the direction i need it to be, i have 2 follow up issues/querries please

1) Strictly relating to the model you shared with me above, in the case of Scenario 2024, that column should provide the same cost as in the column cost 2024 if that required input is not selected.

i.e. that how its behaving now, but plastic casting should also show 8, processor show 18, sensor 5. The logic should be as follows :

IF category is selected,

     then for selected required input multiply COST 2024 * Parameter Value,

else for all other required inputs equals cost 2024.

Also when 2 or more are selected it stops working. 

2) Would it be easier to do this differently? i.e. have 6 parameters a user could change as needed. 

Those 6 parameters would change the values in this table.

And then connect this table to the main data through column A primary key, into a new column/measure that would do the cost 2024 multiplied by the parameter value assigned to the required input. 

Thank you in advance, i know that my question is not quite simple. 

[v-jianpeng-msft]

Hi, @改善 

Based on your description, I use the following sample data:

I'm going to use summarize to create a new table with the following parameters:

Table 2 =
SUMMARIZE ( 'Table', 'Table'[Required Input] )

I have established the following relationship between the original table and the new table:

I'm using the following DAX to determine if a field in another chart is selected, and if so, Scenario 2024 is calculated:

Scenario 2024 =
IF (
    ISBLANK ( SELECTEDVALUE ( 'Table 2'[Required Input] ) ),
    BLANK (),
    MAX ( 'Table'[Cost 2024] ) * ( 1 + [Parameter Value] )
)

In other charts, use the parameters of the new table:

Here are the results:

I've provided the PBIX file used this time below. You can adjust Scenario 2024 to suit your actual situation and add more conditions to meet your requirements.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.