creating a white space table

[emma313823]

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())))

[SamWiseOwl]

You need [] around measure names so [CurrYrComm], sorry editing on a phone is hard! 😂

[emma313823]

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"

)

[SamWiseOwl]

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?

[emma313823]

if the principal pays then a data point is entered into the system, however if nothing is received then no data point. When I pull in the measure into the table...it is driven by the check value. I would guess that if in Johanson's case they paid every month from January to June and July they paid nothing...I would still see a cell for Johanson for June-December even though there is no data point.

So what I did was go back into my data and entered a line for a few principals active and inactive and entered a value of zero for the commission check (used a mock check number). I was hoping that by doing so and refreshing I would see black.

Advanced Energy Jan and May - (this one is active)

Johanson Dielectrics May, June, July (this is inactive)

Johanson Technology May and July (this is inactive)

After refreshing, I see black only on Johanson and only in July., so that is weird.

[SamWiseOwl]

@emma313823 
I Think I have it:
 

First create a column with all the required months, join this to your month column and use it in the visual instead of the existing month

Create a measure that returns 0 when there is no data, use this in the values

Make the colour measure check for this 0 and the status

Replace with 0 = if(sum(active[Value]) <> BLANK() , sum(active[Value]),0)

Colour measure =

SWITCH(

    TRUE()

    ,[Replace with 0] = 0 && CALCULATE(SELECTEDVALUE(active[Principal Status]), all('calendar table'[Calendar Month Name])) = "Active"

    ,"Pink"

    ,[Replace with 0] = 0 && CALCULATE(SELECTEDVALUE(active[Principal Status]), all('calendar table'[Calendar Month Name])) = "Inactive"

    ,"Black"

)

I've uploaded a test file here:

original missing rows.pbix

[SamWiseOwl]

This bit should be the name of the column that holds Active or Inactive

SelectedValue(Principals[Status])

[SamWiseOwl]

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!

[emma313823]

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...

[emma313823]

oh wait...i fixed principal status also...and now get this...

[SamWiseOwl]

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!