Multi-purpose formatting Measure

[ells]

I use measures to do standard formatting.
So
if it is a % then there are four colours depending on the value
if its a growth % there are four colours depending on the value (different values to just a % measure)
if its a number there are four colours depending on the value (different values to the above)

What I am looking for is to just have three measures to do this.

So if
I have a table showing Measure1, Measure2, Measure3, Measure4
All 4 are numerical measures.
I can click on each measure in the table and just use one measure to do the formatting rather than have one formatting measure for each column

This means the the measure needs to be aware of the Table cell it is applied to. This is what I can not get my head around.
Thanks
E

[Heather]

A small mockup of data would be helpful for anyone trying to assist with this question.

without that, all I can recommend is that you may want to look at measures like ISINSCOPE, SELECTEDVALUE, and so forth. Using that to determine which column you are on, and then a SWITCH True to return the colors based on the rules needed for that column.

[ells]

@Heather
Not usre about this. I have never used SELECTEDVAUE to get the name of a measure and would not know how to do that. I am also not sure about INSCOPE because on one row surley all the measures are Inscope.

Will add an example.
Thanks
E

[Heather]

this is why a model is advisable, because I can see an instance of having a column that is being used in the visual and being able to use ISINSCOPE

my other recommendation - would be ISSELECTEDMEASURE or SELECTEDMEASURE (these are different)

and I’ve added a link below to a dataset creation tool that may be helpful

Knowledge Base | Get Answers Fast with Enterprise DNA

Incredible Free EDNA Tool for Dataset Creation

All, I wanted to bring to your attention a fantastic free tool created by @sam.mckay that allows you to quickly and easily create datasets and even complete data models to almost any specification. This is incredibly useful for providing sample data to the forum on questions you might have where the actual data contains confidential/sensitive information that is otherwise difficult to mask. It’s also great for creating datasets to test your code, develop examples for teaching/training, etc. T…

[ells]

Example provided
Measure Formating.pbix (23.1 KB)

So I have two numerical values in the table. I want them both to have conditional formating for the back ground colour. For this there is a measure Format Number. Now currently this is baseod on [Total Sales] but I want this to refer to the measure that [Format Number] has been ap[plied to.
In this case I where [Format Number] is applied to Total Sales and Sales Value - the desired behaviour is that for the Sales Value column it will realise it needs to use the measure Sales Value and so -2.00 will be formatted in red.
Hope that helps
E

[ells]

Got to the below code but the Selected Measure does not seem to do what I think it should
Format Number =

/* I want this measure to understand the Measure that it is being applied to /
/ This means that the Variable Measure Value must be able to understand the current cell */

VAR MeasureValue =
IF (
ISSELECTEDMEASURE ( [Total Sales], [Total Sales Value] ),
SELECTEDMEASURE(),
SELECTEDMEASURE()
)

VAR RETURNVALUE =
SWITCH(
TRUE()
,MeasureValue = BLANK() , [Format Colour 0]
,MeasureValue = 0 , [Format Colour 0]
,MeasureValue < 0 , [Format Colour Lowest]
,MeasureValue > 0 , [Format Colour Highest]
,[Format Colour 0]
)

RETURN
RETURNVALUE

We are getting some unexpected results. I can repace the last SELECTEDMEASURE() with 1 and that turns the two Measures green but cant get it to switch on the Measure Name

[Heather]

I have tried turning this every way I can, and I’m not able to use a single measure to conditionally format each of the columns. I honestly thought this should be possible - but it now appears that in order to do this you probably need to do one of the following:

1. do this in a Matrix table,
   which means you would need to build a helper table to join the Product name values with your Measure Names (to create the columns in the visual). Then you would have a measure to return the number based on the product name/column. Sam McKay has detailed this technique in several of his videos and teachings on the Accounting style tables. I have linked to a couple of these below.

2. using a calculation group - this would again create a single measure to return the values, so your conditional format would again be looking at only one measure.

The option I would probably choose for myself - create 4 versions of the format measure. Name each of the format measures for the measure you are formatting.

Financial Reporting in PowerBi - custom subtotals

Unfortunately there is no easy way to complete this currently. I’ve seen a way to create subtotals but I don’t particular like it that much as it’s way too complex. The best examples I have currently are below. I will also be covering financial reporting again during the next learning summit event. [Integrated Financial Reporting - Power BI Techniques For Accounting & Finance] [Creating PnL Statements In Power BI - Financial Reporting Example] I’m sure MS will be l…

Integrated Financial Reporting - Power BI Techniques For Accounting & Finance

[ells]

Thanks,
As I have a serious number of measures and the ability for the users to personalive the visuals I was hoping to just get a handfull of formatting measures that would react to the measure.
Will look through the two options.
Thanks
E