Conditional Formatting measure

[ejosling]

Good Day All

I have a matrix table consiting of Sites/Branches as the rows, Months as the columns and sales values as the values (and the year as a filter)

Branch	Jan	Feb	March	April
Branch#1	1 582 837	1 875 872	1 786 478	2 350 070
Branch#2	3 022 305	4 369 532	4 835 868	4 226 582

Now I want to highlight, for each row (branch), the highest and loweset month sales total.  Thus for Branch#1, the highest total is in Month April and the lowest in Jan.  For branh#2, the highest is in March and the lowest in Jan.

The table can also span accross different years.

I'm struggling to get the measure working that calcuates these values and I'm getting "Out of memory" when executing this measure.

Can someone please help me and maybe point out if this is the correct way of writing the measure?  I use then this measure to do conditional formatting on the sales values.

The measure looks like this:

MAX/MIN Values = 
VAR mymin = MINX( GROUPBY( ALLSELECTED('Sales Transactions'), Branch[Branch],Dates[FY],Dates[Month Name],
"Min Sales by branch by month",SUMX( CURRENTGROUP(), 'Sales Transactions'[YPSAMOUNTEXCLALLDISCOUNT])
), [Min Sales by branch by month]
)
VAR mymax = MINX(GROUPBY(ALLSELECTED('Sales Transactions'), Branch[Branch],Dates[FY],Dates[Month Name],"Max Sales by branch by month",SUMX( CURRENTGROUP(), 'Sales Transactions'[YPSAMOUNTEXCLALLDISCOUNT])
), [Max Sales by branch by month]
)
RETURN
SWITCH(
TRUE(),
mymin = sum('Sales Transactions'[YPSAMOUNTEXCLALLDISCOUNT]), "#FF7F01",
mymax = sum('Sales Transactions'[YPSAMOUNTEXCLALLDISCOUNT]), "#2AAAFF"
)

I have seperate date table, sales transaction table and a branch table.

Thanks!

[v-joesh-msft]

Hi @ejosling ,

Try the measure below:

MAX/MIN =
VAR _table =
    GROUPBY (
        ALLSELECTED ( 'Sales Transactions' ),
        Branch[Branch],
        Dates[FY],
        Dates[Month Name],
        "Min Sales by branch by month", SUMX ( CURRENTGROUP (), 'Sales Transactions'[YPSAMOUNTEXCLALLDISCOUNT] )
    )
VAR _table1 =
    FILTER ( _table, Branch[Branch] = MAX ( Branch[Branch] ) )
VAR mymin =
    CALCULATE ( MINX ( _table1, [Min Sales by branch by month] ) )
VAR mymax =
    CALCULATE ( MAXX ( _table1, [Min Sales by branch by month] ) )
RETURN
    SWITCH (
        TRUE (),
        mymin == SUM ( 'Sales Transactions'[YPSAMOUNTEXCLALLDISCOUNT] ), "#FF7F01",
        mymax == SUM ( 'Sales Transactions'[YPSAMOUNTEXCLALLDISCOUNT] ), "#2AAAFF"
    )

Results are as follows:

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYgUSyLTDQhNne4IIY...

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.

[ejosling]

Hi @v-joesh-msft , thanks for reply and the measure!  It resolved the out-of-memory issue, however, the results I see when I apply the measure on my data model is not correct.

I downloaded and tested it with your example and it functions as expected - thus I'm not sure why the same does not work on my model?

Below 'n screenshot of my results:

As you can see, some rows do not have any formatting and others only one - where each row should have two colours - one for minimum value and one for maximum value.

One difference is that my date table is marked as a date table?

The other differences is on my relationships from the Sales Transactions table to other lookup tables as well.

Any ideas?

Eddie

[v-joesh-msft]

Hi @ejosling ，

There may be problems with the model, could share your sample pbix file for me to have a test if you don't have any Confidential Information, a small amount of data will do.

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.