How to create single delta measure for all measures

[subburaj]

Hi Friends - Require your help please. Please find the attached screenshot for your reference. This is a matrix table, Fiscal Year (FY18, FY19) are in columns and measures are in rows. I need to create a single delta measure (FY19 minus FY18/FY18) for all measures. The new delta measure would in column, next to FY19. Kindly help.

[AkhilAshok]

You could enable the Column Sub-Total and adjust the DAX to potentially achieve this:

Measure 1 w Diff =
VAR fy18 =
    CALCULATE ( [Measure 1], [FISCAL YEAR] = "FY18" )
VAR fy19 =
    CALCULATE ( [Measure 1], [FISCAL YEAR] = "FY19" )
RETURN
    IF ( ISFILTERED ( [FISCAL YEAR] ), [Measure 1], ( fy19 - fy18 ) / fy18 )

[subburaj]

This works..thanks... i want to apply conditional formatting to column sub total..like font color to show red when it is positive and green when it is in negative or up/down basedd on column sub total. Kindly help 

[AkhilAshok]

Unfortunately, you cannot apply conditional formatting on Grand Totals. A workaround would be the following:

1. Create a new Calculated Column in the table: Delta = "Delta"

2. Create a new Matrix table, with Delta as the Column, and [Measure 1 w Diff] as Values.

3. Disable sub-totals, show value on Rows, and disable word wrap for Row Header

4. Reduce the size of Row Header to mininum, so that the measure names are not visible.

5. Enable the conditional formatting on this table based on your requirement

6. Arrange it close to the previous matrix table, so users get a visual feel that it is a single table

Hope this helps.

[subburaj]

Awesome..you are legend..one last question... I am unable to achive your point 4 (Reduce the size of Row Header to mininum, so that the measure names are not visible) and 5 (Enable the conditional formatting on this table based on your requirement). I have tried but could not. Also would be great if i could get your contact details.

[AkhilAshok · Accepted solution]

For point 4, you just have to hover the mouse on the column header, click/drag and reduce the column size to minimum.

Point 5 is just adjusting about adjusting your rule. I see from screenshot, you only gave >=0 check, but in the table it is negative (-8). Can you try adding a rule for greater than or equal to -100 & less than 0 and check?

[Ashish_Mathur]

Hi,

I doubt if that is possible.  There will have to be a seperate growth measure for each measure you have computed.  However, it is possible that you have had to write so many measures because your base data is not well structured.  One can ascertain that only after seeing your PBI file.