Custom measure - Conditional formating for quarterly Target

[romovaro]

HI

I need to create quarterly goals based on how many months a project has been on Hold. I created a custom field called 

BL in Months = DATEDIFF('Slippage Report'[Signed], TODAY(), MONTH)

En another field:

BL Ageing  =

IF('Slippage Report'[BL in Months] <= 12, "Less than 12 Months",

IF('Slippage Report'[BL in Months] <= 24, "Between 12-24 Months",

"More than 24 Months"

))

Where I can display the totals in %

Now I need to add the quarterly targets. I have 2 products and a quarterly Target for every quarter and every "BL ageing type"

Any idea how to create the quarterly forecast using the BL in Months and Bl Ageing type

BL AGEING	Product	Qtr Target	Quarter
Less than 12 Month	A	38,00%	Q1
Between 12 - 24 Months	A	52,00%	Q1
More than 24 Months	A	10,00%	Q1
Less than 12 Month	A	40,00%	Q2
Between 12 - 24 Months	A	52,00%	Q2
More than 24 Months	A	8,00%	Q2
Less than 12 Month	A	40,00%	Q3
Between 12 - 24 Months	A	54,00%	Q3
More than 24 Months	A	6,00%	Q3
Less than 12 Month	A	45,00%	Q4
Between 12 - 24 Months	A	50,00%	Q4
More than 24 Months	A	5,00%	Q4
Less than 12 Month	B	70,00%	Q1
Between 12 - 24 Months	B	20,00%	Q1
More than 24 Months	B	10,00%	Q1
Less than 12 Month	B	75,00%	Q2
Between 12 - 24 Months	B	15,00%	Q2
More than 24 Months	B	10,00%	Q2
Less than 12 Month	B	80,00%	Q3
Between 12 - 24 Months	B	15,00%	Q3
More than 24 Months	B	5,00%	Q3
Less than 12 Month	B	80,00%	Q4
Between 12 - 24 Months	B	10,00%	Q4
More than 24 Months	B	5,00%	Q4

Thanks

Conditional Formatting Using Custom Measure

[amitchandak]

@romovaro , your heading vs your description is creating confusion.

can you share the expected output?

refer if needed

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[romovaro]

Sorry for that amitchandak.

I guess my expected outcome is to be able to add the target in my graph or table.

I cannot see the "conditional formatting option" in my custom measures...but maybe my heading is confusing.

How to add the target (in %) to my data in power bi.

Qtr target table below. For every quarter, there is a % of BL ageing (KPI). 

BL AGEING	Product	Qtr Target	Quarter
Less than 12 Month	A	38,00%	Q1
Between 12 - 24 Months	A	52,00%	Q1
More than 24 Months	A	10,00%	Q1
Less than 12 Month	A	40,00%	Q2
Between 12 - 24 Months	A	52,00%	Q2
More than 24 Months	A	8,00%	Q2
Less than 12 Month	A	40,00%	Q3
Between 12 - 24 Months	A	54,00%	Q3
More than 24 Months	A	6,00%	Q3
Less than 12 Month	A	45,00%	Q4
Between 12 - 24 Months	A	50,00%	Q4
More than 24 Months	A	5,00%	Q4
Less than 12 Month	B	70,00%	Q1
Between 12 - 24 Months	B	20,00%	Q1
More than 24 Months	B	10,00%	Q1
Less than 12 Month	B	75,00%	Q2
Between 12 - 24 Months	B	15,00%	Q2
More than 24 Months	B	10,00%	Q2
Less than 12 Month	B	80,00%	Q3
Between 12 - 24 Months	B	15,00%	Q3
More than 24 Months	B	5,00%	Q3
Less than 12 Month	B	80,00%	Q4
Between 12 - 24 Months	B	10,00%	Q4
More than 24 Months	B	5,00%	Q4

[romovaro]

Thanks Amichandak

I used the "Countrows" formula to know the total amount of lines and then i present the % of the 

3 options x quarter.

Less than 12 Months", "Between 12-24 Months"& "More than 24 Months"

With this , I don't have the option of conditional Formatting display. I Guess a Formula is the only way?

[Ashish_Mathur]

Hi,

I am still not clear with your expected result.  Could you kinldy show the result in a Table format.  Preferably in an Excel file with your formulas so that those formulas can be translated in the DAX language.

[romovaro]

Hi Ashish,

The current BL Ageing x Quarter I get from our current report . Now Management wants to add Quarterly targets and compare vs current. Quarterly targets are not included in the system. They are fix numbers that need to be included in the report. (Manual Measure)

I created the custome fields

BL in Months = DATEDIFF('Slippage Report'[Signed], TODAY(), MONTH)

and

BL Ageing 2 =

IF('Slippage Report'[BL in Months] <= 12, "Less than 12 Months",

IF('Slippage Report'[BL in Months] <= 24, "Between 12-24 Months",

"More than 24 Months"

))

Now in need to create the target measures to compare with my current numbers:

My idea of representation would be table format: (maybe with color changinf if current is higher than target)

OR maybe a more visual representaton using clustered column chart for the current and then a line for target...or column...

But right now I guess the issue is to be able to represent current vs target.

Thanks