Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dineshj23
Helper I
Helper I

Conditional Formatting with double values

Hi all, 

I have the below data imported into PowerBI. I created dates for each of the sales amounts (Sales 1, 2, 3 etc.) with relationship to the  one main calendar. For the matrix visual, I created conditional formatting where if a month is selected you can see the relevant approved or unapproved sale amounts. If it is approved, the individual cell element turns green. 

dineshj23_1-1680548574354.png

However, when stage 1 or stage 2 (with the same sales order no.) has one approved field, the whole cell element turns green. Below is a snapshot of what I am trying to say. 

dineshj23_4-1680549183513.png

The collapsed version shows that everything is approved. However, when you drill down and expand on the line, you see that only stage 1 has been invoiced and some of the stage two haven't been approved for invoicing. 

dineshj23_5-1680549416744.png

I tried to change the background conditional formatting with the below figure to "First" and "Last" but it didn't work. 

dineshj23_6-1680549616253.png

 

How can I correct this?

 

 

3 REPLIES 3
dineshj23
Helper I
Helper I

@v-stephen-msft Thank you for looking into this. Below is the example of what I mean. 

Here's the expanded version of a matrix visual showing Stage 3 of "4564" not approved and not highlighted. 

dineshj23_0-1680885343658.png 

But the condensed version shows that "45664" is green. However, there's stage 3 that has not been approved. Someone looking at the condensed visual will think that the sales order is all approved and complete when it's not. 

dineshj23_1-1680885579765.png

Any way to fix this? Here's my field value measure I created to highlight this as green. 

Color1 = var m = max(Sheet1[Sales Status])
    Return
        CALCULATE(
            if (m = "Approved","#90EE90",""))

 

 

 

Hi @dineshj23 ,

 

Unfortunately, there seems to be no way around this at the moment. If you can, you can create a relevant idea and vote on:

Ideas (powerbi.com)

 

Best Regards,

Stephen Tao

v-stephen-msft
Community Support
Community Support

Hi @dineshj23 ,

 

You could try creating a measure for conditional formatting.

Color = var _count1=CALCULATE(COUNT('Table'[Saels Order No.]),FILTER(ALLSELECTED('Table'),[Saels Order No.]=MAX('Table'[Saels Order No.])&&[Sales Status]="Approved"))
var _count2=CALCULATE(COUNT('Table'[Saels Order No.]),FILTER(ALLSELECTED('Table'),[Saels Order No.]=MAX('Table'[Saels Order No.])))
return IF(_count1=_count2,"Green")

vstephenmsft_0-1680679089059.png

After the conditional formatting is applied, all must be approved to turn green. The logic is to group according to the sales order number, count the number of "Approved" and the number of sales order numbers, and return green if equal.

vstephenmsft_1-1680679108802.png

 

   

                                                                                                                                                         

Best Regards,

Stephen Tao

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.