cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
subburaj Regular Visitor
Regular Visitor

How to create single delta measure for all measures

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.

Question.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
AkhilAshok Established Member
Established Member

Re: How to create single delta measure for all measures

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?

View solution in original post

6 REPLIES 6
Super User
Super User

Re: How to create single delta measure for all measures

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AkhilAshok Established Member
Established Member

Re: How to create single delta measure for all measures

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 Regular Visitor
Regular Visitor

Re: How to create single delta measure for all measures

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 Established Member
Established Member

Re: How to create single delta measure for all measures

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 Smiley Wink

 

Hope this helps.

subburaj Regular Visitor
Regular Visitor

Re: How to create single delta measure for all measures

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 Established Member
Established Member

Re: How to create single delta measure for all measures

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?

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 155 members 1,973 guests
Please welcome our newest community members: