Dynamic calculated column based on slicer selection

[Vennela]

Hi,

I have a situation where the user wants to select a value from the slicer and the Power BI should calculate the columns based on the selected value. 

Below is a clear explanation of the issue. 

I have table 'A' with Threshold values that go into the slicer (1% - 100%)

Table 'B' with ID column, Network column (contains 1 or 0), and a calculated measure, Rate (in percentage format)

Now, I want to create a calculated column 'Applicable' in Table 'B' that says if the network is 1 and Rate >= selected threshold value, then 1 else, blank.

Table 'A'

Table 'B'

'Rate' measure in Table 'B' looks like below. It is calculated using a different table.

I have created a measure 'Applicable', 

Applicable = IF(SUM('Table B'[Network])>0,IF([Rate]>=SELECTEDVALUE('Threshold'[Threshold]),1,0),0)

So whenever I select a value in the 'Threshold' slicer this measure is giving me the right result for each line in the visual but, the grand total is showing as zero (probably it is taking the average). Below is the screenshot.

I want the grand total to show the sum of the 'Applicable' measure (In this case its 3). 

Is there a way to use selectedvalue in the column calculation?

I tried using the same dax to calculate a column but the selectedvalue dax is returning a null value.

Any help on this would be appreciated. 

Thanks,
Vennela

[Vennela]

Hi @amitchandak, Thanks for your response. your solution is working if I create a measure. I can see the grand total showing the sum of the field. 
But, is there a workaround to create a column instead of a measure? as I have to use this column in further calculations which would be at row level hence, it should be a calculated column.

[Anonymous · Accepted solution]

Hi @Vennela ,

I'm afraid that creating calculated columns doesn't do what you want (dynamically displaying values based on slicer selection). As @amitchandak said, creating a calculated column can't get the values selected by the slicer. You can check the following blogs for the answer...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

There is a big difference between calculated columns and measures. The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report. A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.

---

Best Regards

[amitchandak]

@Vennela , You need to create measure as column do not take slicer value

Try like

Applicable =

sumx(Table, calculate( IF(SUM('Table B'[Network])>0,IF([Rate]>=SELECTEDVALUE('Threshold'[Threshold]),1,0),0) ))

or

sumx(values(Table[ID]), calculate( IF(SUM('Table B'[Network])>0,IF([Rate]>=SELECTEDVALUE('Threshold'[Threshold]),1,0),0) ))