Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create a calculated column using another calculated column

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. 
 
 
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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

     

6 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Sure. 

       

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Samarth_18
        Community Champion
        4 years ago

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