Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Missed icons for table conditional formatting

Hi everybody,   I think my issue is quite simple but I'm really unable to find a solution.   I create a simple table with these data and tried conditional formatting with icons.    ...
Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    Hi Anonymous ,

    You can use the IF statement to nest, and change the measure: "% CT 2 with area" in the previous demo to the following formula：

    % CT 2 with area =
IF (
    ISINSCOPE ( 'Table (2)'[ Area] ),
    DIVIDE (
        SUM ( 'Table (2)'[Qty] ),
        CALCULATE ( SUM ( 'Table (2)'[Qty] ), ALLSELECTED ( 'Table (2)'[ Area] ) )
    ),
    DIVIDE (
        SUM ( 'Table (2)'[Qty] ),
        CALCULATE (
            SUM ( 'Table (2)'[Qty] ),
            ALLSELECTED ( 'Table (2)'[MercatoAgente] )
        )
    )
)

    Best Regards,

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you v-joesh-msft 

 

With your hint the example with GT works fine, even if it's weird to see it working while has different conditional formatting settings than RT visual.

 

Instead, visual with CT is not fine: all the icons stay green! I thought I could need to add Area field (I am drilling down from MercatoAgente to Area) to the ALLSELECTED function in CT measure, but doing it something strange happens:

 

Some icon colors are good, but why are 2.580 or 200.200 red??

 

Thanks

v-joesh-msft's avatar
v-joesh-msft
Solution Sage
6 years ago

Hi @ nick93,

I found the formula used in the first answer:：RT with ALLSELECTED('Table')，GT with ALLSELECTED('Table'[Type]) may be confusing, using RT with ALLSELECTED('Table'[Type]), GT with ALLSELECTED('Table') might make a better understanding. RT refers to row total, and GT refers to grand total.

When you add a new field to the row of the matrix, the row context will also change. I have made some examples and hope to have a more intuitive understanding;

1: There is only one field in MercatoAgente in Rows:

2: There are two fields MercatoAgente and Area in Rows:

(1) Presentation of CT/RT/GT:

(2) Presentation of different Rows references in ALLSELECTED:

 

Looking at the rendering of each of the different fields in the screenshot, you may find the difference and find the result you want.

 

Here is a demo , please try it：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ea2Xmq1i4N5Kp_U8hSNaxdMBePPQdUFuXMWdbnurmkA_qQ?e=9je4Fa

 

Best Regards,

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

  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    Hi Anonymous ,

    You can use the IF statement to nest, and change the measure: "% CT 2 with area" in the previous demo to the following formula：

    % CT 2 with area =
IF (
    ISINSCOPE ( 'Table (2)'[ Area] ),
    DIVIDE (
        SUM ( 'Table (2)'[Qty] ),
        CALCULATE ( SUM ( 'Table (2)'[Qty] ), ALLSELECTED ( 'Table (2)'[ Area] ) )
    ),
    DIVIDE (
        SUM ( 'Table (2)'[Qty] ),
        CALCULATE (
            SUM ( 'Table (2)'[Qty] ),
            ALLSELECTED ( 'Table (2)'[MercatoAgente] )
        )
    )
)

    Best Regards,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi v-joesh-msft and thank you one more time

     

    Ok, it looks fine, now all what you explained me works.

    I have just one last doubt: my aim was to create a conditional formatting which worked fine both if I didn't drill the table down and stayed at MercatoAgente level and if I drilled it down to Area. I would like to create a single measure which formats the table as you see in the following images: in image1 the total to choose the icons should be the column total, in image2 icons should be based on the column total of each MercatoAgente class, and not the whole total.

     

    Image 1: here the ALLSELECTED function contains MercatoAgente

     

    Image 2: here the ALLSELECTED function contains Area

    I thought I could create a table formatted this way with a single measure. Is it possible?

     

    Thank you again

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Great v-joesh-msft, thank you very much!

     

    I would like to mark your answer as the solution but I should log in with my old account (nick93) and right now I have some issues I am trying to solve with Support, so for the moment saldy I can't.

     

    Thanks for your help!