Using IF Statement for Date to Separate Dollar Amounts in other Columns; along w/ Conditional Format

[CA8172]

I need help with two issues.

First, I created a Matrix that shows the 'Merchant' on the left (rows) and the 'Day' (1-31) on the top (column).  I originally created a DAX "IF" formula to provide a number, -5 or 10 based on whether the Journal Amt was less than or greater than Sent Amt as well as one for Journal to Capture Amt and Sent Amt to Capture Amt.  Then used Conditional Formating at Font and Background Level to highlight the 'Day' that needs to be investigated if they neither matches. [See below]

However I noticed that it is basing it off the "Total" and not for the specific date.  I'm sure I can use an additional condition or a USERELATIONSHIP, but have failed on both attempts.  How do I say I want to provide the IF Formula for Journal->Sent, Journal->Capture & Sent->Capture but include the specific Date/Day & Merchant?

Below are the results that I was referring to above.  I was also thinking of including a SUM or VARIANCE based once on the specific Date/Merchant on a Table visualization (like below) to use Conditional Formating to highlight the box (see the other screenshot below) that would show the discrepancies in the amounts for easier checks and balances.

[Below screenshot would use conditional formating with a Table visualization to highlight the condition that Journal is less than Sent and/or Capture Amt]

Any thoughts/help would greatly be appreciated.  I will continue to think of wording on what I want see if I can find something via Google.

Thanks!

[TomMartens · Accepted solution]

Hey,

unfortunately I do not fully understand the criteria, that have to be met to colorize the background.

Nevertheless I created the following measure:

vizAid Journal Background = 
var thisJournal = CALCULATE(SUM('Table1'[Journal]))
var thisSent = CALCULATE(SUM('Table1'[Sent]))
var thisCapture = CALCULATE(SUM('Table1'[Capture]))
return
IF(
    OR(thisJournal < thisSent, thisJournal < thisCapture)
    ,"lightblue"
    ,BLANK()
    )

After that I created a conditional formatting for the column "Journal" using 3 steps and assigned the measure to the background formatting:

First

Second

Third

This leads to the following table:

Just adopt the condition in the measure to your needs 🙂

Be aware that you can also use a valid hexcode like #232323 (you have to use quotation marks) instead of the name of a color.

Hopefully this is what you are looking for!

Regards,

Tom

[CA8172]

Tom,

Thanks for that help.  I did change your formula to make a few minor changes, but that was so helpful!  Defintely saved me from beating my head.

As for the other piece.  I figured out how to do with with using an IF statement using OR on some clauses and AND on another:

RESEARCH_COLUMN = IF('vw_Recon'[Credit Amount]<>'vw_Recon'[Online Amount] || 'vw_Recon'[Credit Amount]<>'vw_Recon'[Reconcile Amount] || 'vw_Recon'[Online Amount]<>'vw_CC_Recon'[Reconcile Amount] && 'vw_Recon'[Recon Date] = RELATED(tbl_Calendar_New[FileDate]),50,BLANK()))

To get this result:

This shows which Merchant ID's have an "issue" and need to be researched accordingly.