Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CSmith_HW
Frequent Visitor

Difference column in matrix with filtered columns

Hi there,

I have a matrix that shows breakdown of events and countries that looks something like this:

 

CountryEvent 10803Event 14333

Italy

68
UK4332
US5154


I want to add a column to the end of this that calculates the growth rate between these events, however, the way this report works is that a slicer will be used to select which events are displayed, so the measure cannot just be "Event 10803" - "Event 14333" because there are tons of event combinations that could be selected. What's more I want the growth rate to be based on event timing (this field is on the same object as the events), so the most recent event always gets placed as the first number. I calculated a similar measure (taking into account dynamically selected events, event date and revenue) by using the following code:

Event Revenue Growth = 
VAR SelectedCampaigns = 
    VALUES ( Event[Start Date] )
VAR OldEvent = 
    CALCULATE ( [Event Revenue (£)], FIRSTDATE( SelectedCampaigns ) ) 
VAR RecentEvent = 
    CALCULATE ( [Event Revenue (£)], LASTDATE( SelectedCampaigns ) ) 
VAR Difference = RecentEvent - OldEvent
RETURN
    DIVIDE ( Difference, OldEvent, 0)

This only works as a card however, as when I drag it into a table it shows 0% (because firstdate and lastdate pull out the same event when applied to a table with event as a column/row). Not sure if this code will help as it's a slightly different scenario but I thought it may prompt some ideas.

Any help would be great - thanks

1 REPLY 1
Greg_Deckler
Super User
Super User

Is that the source data? Example source data would be helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors