Forum Discussion

ejosling's avatar
ejosling
New Member
6 years ago

Conditional Formatting measure

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 A...
Need Help
v-joesh-msft's avatar
v-joesh-msft
Solution Sage
6 years ago

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/EYgUSyLTDQhNne4IIYmqq68BT9Jref_TaQ5gZ7VGCVaRyg?e=kBC5CM

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's avatar
ejosling
New Member
6 years ago

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's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    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.