Error while creating calculated fields based on Field Parameter selected

[Khushboo9966]

 Hello,

I have attached my PBIX file.  I am facing an error when I use field parameter selection in my calculated formula. The formula(_Standardize_new) won't throw an error when I input a static number but return an error when I use field parameter selection(_Standardize).

TestFile

Any help is appreciated. 

[v-yangliu-msft]

Hi  @Khushboo9966 ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

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

[v-yangliu-msft]

Hi  @Khushboo9966 ,

Are you referring to the incorrect total row calculation? You can consider using the IF() + HASONEVALUE() function to determine if it’s a total. If it is, you can perform calculations based on your requirements, such as average, sum.

Measure 2 =
var _table=
SUMMARIZE('Table',[Group],"Value",[Measure])
return
IF(
    HASONEVALUE('Table'[Group]),[Measure],
    SUMX(
        _table,[Value]))

Best Regards,

Liu Yang

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

[Khushboo9966]

@v-yangliu-msft  The solution you presented here works when the [Value] is a calculated column. In this case we are using measure name 'measure' to calculate the %. How can I accomplish the sum using the measure option you suggested in the previous pbix file?

[v-yangliu-msft]

Hi  @Khushboo9966 ,

As far as I know, the calculated columns are not affected by the value of the slicer, the calculated columns are populated once when the data is refreshed and are not refreshed again, if the interaction is changed, and the slicer is changed, the base row context and any calculations performed at load time are not affected.

And slicers can be used for measure, which are dynamic fields that are calculated on demand. Expressions are stored inside, which change dynamically with what the slicer selects and how the slicer interactions are defined.

And SELECTEDVALUE('Month No'[Month No]) is the value in the 'Month No' table, there is no connection with the original table to get the corresponding [Month No], which will lead to null values.

You might consider using measure, which displays the corresponding value as the slicer is selected:

Measure =
SWITCH( true(),
        MAX('Table'[_Totals]) = 1, DIVIDE(MAX('Table'[_Count by Name by _totals]) *10, DIVIDE(1555,SELECTEDVALUE('Month No'[Month No])*12)),
       MAX('Table'[_Totals]) = 2, DIVIDE(MAX('Table'[_Count by Name by _totals]) *13, DIVIDE(1555,SELECTEDVALUE('Month No'[Month No])*12)),
        MAX('Table'[_Totals]) = 3, DIVIDE(MAX('Table'[_Count by Name by _totals]) *17, DIVIDE(1555,SELECTEDVALUE('Month No'[Month No])*12)),
        MAX('Table'[_Totals]) = 4, DIVIDE(MAX('Table'[_Count by Name by _totals]) *40, DIVIDE(1555,SELECTEDVALUE('Month No'[Month No])*12)),
        MAX('Table'[_Totals]) = 5, DIVIDE(MAX('Table'[_Count by Name by _totals])*75, DIVIDE(1555,SELECTEDVALUE('Month No'[Month No])*12)),
        0)

This is the related document, you can view this content:

Solved: DAX Table not responding to slicer values - Microsoft Fabric Community

Solved: Using Slicer values in a calculated column - Microsoft Fabric Community

Calculated Columns and Measures in DAX - SQLBI

Best Regards,

Liu Yang

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

[Khushboo9966]

@v-yangliu-msft  Thank you for your explanation. 

The measure works just fine, but it doesn't add the row total correctly. I wonder if it is because of MAX() function. Is there a work around?