Forum Discussion
creating a white space table
All,
I'm struggling with how best to approach this. I thought conditional formatting may help, but I think I will be very limited. Let me explain what I'd like to accomplish in a Power BI matrix table.
The owners of the company want a report to see current year commission payments by month for every Principal (account) paying us a commission payment.
Some principals are active and others can go inactive during a year. I've created a matrix table grouped by Active and Inactive and using conditional formatting show any cell that is blank as pink.
Expanded...
While not horrible, the owners need further tweaking.
Active principals should pay monthly, but it often happens where a payment for a month may be skipped. If you look at Diodes...they skipped paying in February and doub-paid in March.
1. I need to have any cell where a principal has skipped a payment, but is a status of Active be white - so no change or color.
2. I need to have any cell where a principal has not paid (cell is blank) with a status of Inactive be colored black.
At a glance the owners when the owners see black they know that they do not need to forecast and that no more commissions payments will be received from X point forward, whereas, if they see white, they know a principal missed a payment and action needs to be taken to ensure payment is brough current in the next month.
I have no idea how to accomplish this or if I can even do so. I'm hoping that one of the gurus in this forum may have some creative way to help accomplish this.
Status comes from the table Principals[status] and the matrix table is using a measure called CurrYrComm. I wanted to create a measure that I would not have to manually keep adjusting each year and have it more dynamically change.
CurrYrComm =
CALCULATE(SUM(tblIncomingCommissions[Check_Value]),
FILTER('Calendar_Dates',
Year('Calendar_Dates'[Date])=YEAR(Today())))
10 Replies
Hi ema
If I have understood what you are asking we need to create a new measure:
Colour measure =
Switch(true(),
SelectedValue(Principals[status] ) = "Active" && CurrYrComm <> BLANK()
,"White" --Or use "#FFFFFF00" for clear
,SelectedValue(Principals[status] ) = "Active" && CurrYrComm = BLANK()
,"Black"
,"Pink"
)
Then assign this under Cell Elements choose Background color and click the Fx symbol.Change the dropdown in the top left to Field value and select your measure.
If this has worked please mark as a solution for others to find 🙂
Let me know if it doesn't!Hi emma313823
You want to create a new measure that will change the background colour.
Some thing like this:
Colour measure =
Switch(
true()
,SelectedValue(table[PrincipleStatus]) ="Active" && CurrYrComm = BLANK()
,"White"
,SelectedValue(table[PrincipleStatus]) ="Inactive" && CurrYrComm = BLANK()
,"Black"
,"Pink"
)
Select your Matrix, got to Cell Elements, Background colour, change the drop down to Field Value and choose your measure.
Repost, apologises if the last one broke a forum rule!I just tried it and get this.
but...I noticed that principle is spelled wrong so i changed it to principal and entered this....
WhtSpcColor = Switch(true(),SelectedValue(Principals[Status]) ="Active" && CurrYrComm = BLANK(),"White",SelectedValue(Principals[Status]) ="Inactive" && CurrYrComm = BLANK(),"Black","Pink")but still get this...oh wait...i fixed principal status also...and now get this...
This bit should be the name of the column that holds Active or Inactive
SelectedValue(Principals[Status])
You need [] around measure names so [CurrYrComm], sorry editing on a phone is hard! 😂
Hi...got it to work, however here are a couple of screenshots, something seems off.
Active principals went all pink - no payment cells went white (so that's great), but the black should show for all princpals with no payment (blank cell) status of inactive. So in the 2nd screenshot Johanson, Northern Tedh and Wakefield should show black background in for the white cells.
I went back and looked at the DAX. In line 5 for the inactive status...&& is bringing back a single result right?
So if inactive and current year commisison measure is blank, then make the cell black, else pink?
I think I need to drop the pink altogether. My original two objectives...
1. I need to have any cell where a principal has skipped a payment, but is a status of Active be white - so no change or color. I THINK THE FIRST PART DOES THE TRICK.
2. I need to have any cell where a principal has not paid (cell is blank) with a status of Inactive be colored black.
How would I modify this to remove the pink and just be black?
This is what is being used in the measure now.
WhtSpcColor = Switch(true(),SelectedValue(Principals[Principal Status]) ="Active" && [CurrYrComm] = BLANK(),"White",SelectedValue(Principals[Principal Status]) ="Inactive" && [CurrYrComm]= BLANK(),"Black","Pink")I changed it to this, but it's not working. No cells populate black at all.WhtSpcColor = Switch(true(),SelectedValue(Principals[Principal Status]) ="Active" && [CurrYrComm] = BLANK(),"White",SelectedValue(Principals[Principal Status]) ="Inactive" && [CurrYrComm]= BLANK(),"Black")This had me scratching my head!
It never occured to me to ask, do the blank rows actually exist in the data?
I suspect that there is no row in the data for Company = Johnson, Month = May
If there is no row we can't change it's colour because it technically doesn't exist.
How does the data look in the back end before you put it in the visual?
Did you pull the data in an unpivot it by any chance?
