Column calculation dependent on slicer

[Anonymous]

Hello,
I'm looking for solution in Power BI, which I have implemented in excel using VBA. 

Function in column 'Cost' is a sum of values from 'Additional Cost Table' and formula is changing basing on checkboxes values. Changes in column 'Cost' affects result in column 'Margin'. In Power BI dashboard I would like to show chart with Margin and use Slicer to add or remove some additional cost calculated in 'Additional Cost Table'. The problem is that each additional cost is calculated per month and formula in column 'Cost' is also depended on month value in first column of the same row.

[v-xuding-msft]

Hi @Anonymous ,

According to the description, I created a simple sample. 

• Firstly, I unpivot the three values columns in Additional Cost Table. Because it is calculating for Power BI based on columns rather than rows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUTI0MACSRmDSGEjG6kQruaUmFcGkTcHSYNIYSMbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, value1 = _t, value2 = _t, value3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"value1", Int64.Type}, {"value2", Int64.Type}, {"value3", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Data"}, {"Attribute", "Value"}})
in
    #"Renamed Columns"

• Create a new table. And use the column as a slicer.

Filter Value Table = VALUES('Aditional Cost Table'[Value])

• Create a measure

Measure = 
CALCULATE(
    SUM('Aditional Cost Table'[Data]),
FILTER(
    ALLEXCEPT('Aditional Cost Table','Aditional Cost Table'[Month]),
    'Aditional Cost Table'[Value] in ALLSELECTED('Filter Value Table'[Value])
    )
)

For more details, please download my sample below.

[Anonymous]

Thanks for quick answer and visualization. This solution looks perfect, but I have problem with the first step - unpivot table.
Values in "Additional Cost Table" are calculated in Power BI basing on another table (these are SUM functions with some specified filters). I tried to use UNION function, but number of rows is not constant, because new months will be added.

I think UNION is a solution, something like this:

Table = 
UNION(
SELECTCOLUMNS('Table', "Month", 'Table'[Month], "Type of value", "Value 1", "Result",'Table'[Value 1]),
SELECTCOLUMNS('Table', "Month", 'Table'[Month], "Type of value", "Value 2", "Result",'Table'[Value 2]),
SELECTCOLUMNS('Table', "Month", 'Table'[Month], "Type of value", "Value 3", "Result",'Table'[Value 3])
)

[Anonymous]

@v-xuding-msft 

On more thing:

when everything in Slicer is unchecked then it's summing all values (same result like all will be checked). Is it possible to show 0 when all is unchecked?

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I modify the formulas based on your temp table, please have a try.

Additional Cost Table = 
UNION(
SELECTCOLUMNS('Table', "Month", 'Table'[Month], "Type of value", "Value 1", "Result",'Table'[Value 1]),
SELECTCOLUMNS('Table', "Month", 'Table'[Month], "Type of value", "Value 2", "Result",'Table'[Value 2]),
SELECTCOLUMNS('Table', "Month", 'Table'[Month], "Type of value", "Value 3", "Result",'Table'[Value 3])
)

Filter Value Table = VALUES('Additional Cost Table'[Data])

Measure = 
var result = CALCULATE(
    SUM('Additional Cost Table'[Result]),
FILTER(
    'Additional Cost Table',
    'Additional Cost Table'[Data] in ALLSELECTED('Filter Value Table'[Data])
    )
)
return
IF(ISFILTERED('Filter Value Table'[Data]),result,0)

For more details, please see the attachment.

[Greg_Deckler]

You will need to use a measure for this. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.