SUMX with user input as filter

[jaypatel23]

Hi all

Using Power BI for a cost estimate. Now I want to show the impact on the total if certain items' cost were changed. Let's say we have Items 1 to 10, an associated quantity and unit cost. 

I have inserted a What if parameter (as a slider) which allows user to provide a new unit cost and then several measures to recalculate the impact. However, this way I have had to hard-code which item it is: i.e. 

delta_cost = SUMX(FILTER(Cost_Table, Cost_Table[Item]="Item 1"),[Quantity]) * [New_Unit_Cost]

Is it possible in PowerBI to make the "Item 1" part dynamic, ideally based on a list (which is essentially the "Item" column in the source table). So the user is able to select the desired item for which the cost is changed using the slider & finally see what impact that has on the total. 

[Anonymous · Accepted solution]

Hi @jaypatel23 

Assuming that you simply want to see a "total-value" (not with items split up into a table/graph) then you can add a slicer with Cost_Table[Item] as value. That way the user can select which item(s) they want to see the change for along with the change in cost.

Then I believe you should be able to use the following measure to calculate the delta-value between the original total cost and the new changed cost (untested):

delta_cost = 
    VAR Selected_Items = VALUES(Cost_Table[Item])
    RETURN
    
    SUMX(
        ALL(Cost_Table),
        Cost_Table[Quantity]) * Cost_Table[Unit_Cost]
    ) 
    - ( SUMX(
            FILTER(
                ALL(Cost_Table), 
                NOT(Cost_Table[Item] IN Selected_Items)
	    ),
	    Cost_Table[Quantity]) * Cost_Table[Unit_Cost]
        )
	+ SUMX(
            Cost_Table,
            Cost_Table[Quantity]) * Cost_Table[New_Unit_Cost]
        )
    )

Please note that I've renamed the column [Unit_Cost] to, yeah, "Unit_Cost", to indicate the column with the old unit cost values in it. The only column that you supplied was the [New_Unit_Cost] which I assume is the parameter.

[v-lionel-msft · Accepted solution]

Hi @jaypatel23 ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[Anonymous · Accepted solution]

Hi @jaypatel23 

Assuming that you simply want to see a "total-value" (not with items split up into a table/graph) then you can add a slicer with Cost_Table[Item] as value. That way the user can select which item(s) they want to see the change for along with the change in cost.

Then I believe you should be able to use the following measure to calculate the delta-value between the original total cost and the new changed cost (untested):

delta_cost = 
    VAR Selected_Items = VALUES(Cost_Table[Item])
    RETURN
    
    SUMX(
        ALL(Cost_Table),
        Cost_Table[Quantity]) * Cost_Table[Unit_Cost]
    ) 
    - ( SUMX(
            FILTER(
                ALL(Cost_Table), 
                NOT(Cost_Table[Item] IN Selected_Items)
	    ),
	    Cost_Table[Quantity]) * Cost_Table[Unit_Cost]
        )
	+ SUMX(
            Cost_Table,
            Cost_Table[Quantity]) * Cost_Table[New_Unit_Cost]
        )
    )

Please note that I've renamed the column [Unit_Cost] to, yeah, "Unit_Cost", to indicate the column with the old unit cost values in it. The only column that you supplied was the [New_Unit_Cost] which I assume is the parameter.