Create a calculated column using another calculated column

[Anonymous]

Hi,

I'm confronting a problem that I'm not able to solve. I request your help on this. 

I created a column by using the formula

Impact_Mean= calculate(SUM(Append1[Total Impact])/SUM(Append1[Weight]),ALLEXCEPT(Append1,Append1[RiskName]))

which is total sum of impact divided by total sum of weight categorized by risk name. 

Similarly, Probability_Mean is calculated.

Probability_Mean= calculate(SUM(Append1[Total Probability])/SUM(Append1[Weight]),ALLEXCEPT(Append1,Append1[RiskName]))

Now I would like to create another calculated column using these two calculated columns i.e. 

TEST = Switch(TRUE(),

Append1[Impact_Mean]=4 && Append1[Probability_Mean]=4,"4",

Append1[Impact_Mean]=4 && Append1[Probability_Mean]=3,"1",

Append1[Impact_Mean]=5 && Append1[Probability_Mean]=5,"2",

"0")

Test is the final calculated column that does not show any error but the result of Test column is always coming out to be 0 even after the condition is true. Test column is not giving the right results. 

I request your advice on the issue confronted. 

Thank you in advance. 

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

Could you please try below code:-

TEST =
SWITCH (
    TRUE (),
    TRUNC ( Append1[Impact_Mean] ) = 4
        && TRUNC ( Append1[Probability_Mean] ) = 4, "4",
    TRUNC ( Append1[Impact_Mean] ) = 4
        && TRUNC ( Append1[Probability_Mean] ) = 3, "1",
    TRUNC ( Append1[Impact_Mean] ) = 5
        && TRUNC ( Append1[Probability_Mean] ) = 5, "2",
    "0"
)

BR,

Samarth

[Anonymous]

Hi,

Thank you for the suggestion. It is giving me Non-Zero values in Column "Test" but these values are not correct as per the condition applied

Thank you in advance

[Samarth_18]

@Anonymous Is it possible for you to share your PBIX file after removing sensitive data?

[Anonymous]

After correcting the data type and applying Trunc, It is working fine now. 

Thank you very much for your help.

[SpartaBI]

@Anonymous can you share a screenshot of the table with these calculated columns and their result.

[Anonymous]

Sure.