Forum Discussion

Fapo's avatar
Fapo
Frequent Visitor
8 years ago
Solved

Success for each subsidiary with DAX

Hello. I want to creat a clustered column chart which shows me the Total by Subsidiary. So far, so good. But I also want each subsidiary to be evaluated according to the following rule: if the sellings on subsidiary A amount to more than 50000, then write "SUCCESS" below its name; if not, write "FAILURE".

 

I tried the following DAX formula:

 

IF(CALCULATE(SUM(Hechos_de_Ventas[TOTAL_ME])>50,000;Hechos_de_Ventas[SUBSIDIARY]); "SUCCESS"; "FAILURE")

 

But it does not work: it creates two bars for each subsidiary, one showing every successful (more than 50000) sale on subsidiary A, and the other showing every falied (less than 50000) sale on the same subsidiary. Apparently, DAX evaluate each row separately instead of summing every sale on subsidiary A, and ONLY THEN evaluating if the RESULT OF THE SUM is a success or a failure (which is wat I want to do).

 

Thanks in advance.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Fapo,

     

    From your description, I created a simple table like below.

     

    Based on my assumption, you want to show "Success" and "Fail" under the category item on X-axix, right?

    In that case, you could create a calculated column, and add it into X-axis of the chart, together with Hechos_de_Ventas[SUBSIDIARY].

    Is success column =
IF (
    CALCULATE (
        SUM ( Hechos_de_Ventas[TOTAL_ME] ),
        ALLEXCEPT ( Hechos_de_Ventas, Hechos_de_Ventas[SUBSIDIARY] )
    )
        > 10,
    "SUCCESS",
    "FAILURE"
)

     

    However, if you want the "Success"/"Fail" to be dynamically displayed on X-axis based on the amount value change, this is not possible. As a workaround, you can create a measure using the same formula as above, and add this measure into Tooltips section of the chart.

     

    Best regards,

    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Fapo,

     

    From your description, I created a simple table like below.

     

    Based on my assumption, you want to show "Success" and "Fail" under the category item on X-axix, right?

    In that case, you could create a calculated column, and add it into X-axis of the chart, together with Hechos_de_Ventas[SUBSIDIARY].

    Is success column =
IF (
    CALCULATE (
        SUM ( Hechos_de_Ventas[TOTAL_ME] ),
        ALLEXCEPT ( Hechos_de_Ventas, Hechos_de_Ventas[SUBSIDIARY] )
    )
        > 10,
    "SUCCESS",
    "FAILURE"
)

     

    However, if you want the "Success"/"Fail" to be dynamically displayed on X-axis based on the amount value change, this is not possible. As a workaround, you can create a measure using the same formula as above, and add this measure into Tooltips section of the chart.

     

    Best regards,

    Yuliana Gu

    • Fapo's avatar
      Fapo
      Frequent Visitor
      8 years ago

      Thank you Yuliana, you are great! Yes, my intention was to display "Sucess" or "Failure" on the X-axis and this worked just fine. I see that my mistake was basically not using the ALLEXCEPT function.

       

      Cheers!