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
Anonymous
Not applicable

Interact with line chart to analyse increase/decrease over weeks

Hi all,

I'm currently trying to find a way to visualise the changes from 1 data-point to another on a line chart. I'm working with the following dummy data:

 

Week EndingCompanyProductSales
19 April 2020Company BProduct 11000
19 April 2020Company DProduct 11500
19 April 2020Company BProduct 22000
19 April 2020Company CProduct 1750
12 April 2020Company CProduct 1500
12 April 2020Company DProduct 15000
12 April 2020Company BProduct 12500
12 April 2020Company AProduct 1250
12 April 2020Company AProduct 2900
12 April 2020Company BProduct 24000
05 April 2020Company AProduct 11500
05 April 2020Company AProduct 22000
05 April 2020Company BProduct 13000

 

My hope is that I can create something that looks roughly like this:

2020-04-21 10_44_47-Untitled - Power BI Desktop.png

When nothing is selected on the line chart, the tables on the right should show which companies disappeared or were added between the 12th April and the 19th April (the latest date available). If a point on the chart is selected, the tables should show which companies disappeared or were added between that time period and the period before, BUT for only the product selected. That said, if the whole week is selected then it should do the same logic but for all products.

In the example above, when no data point is selected the tables should show:

 

New This Week
(blank)

 

Dropped This Week

Company A

 

Any help with this would be much appreciated!

Cheers,

Aaron

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Try this:

 

1. Create measures.

Distinct This Week = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Company] ),
    FILTER ( 'Table', 'Table'[Week Ending] = MAX ( 'Table'[Week Ending] ) )
)
Distinct Last Week = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Company] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Product] ),
        'Table'[Week Ending]
            = MAX ( 'Table'[Week Ending] ) - 7
    )
)
Count of Company = COUNT( 'Table'[Company] )

 

2. Create a Company table.

Company = VALUES( 'Table'[Company] )

 

3. Create a Measure.

Measure = 
VAR ThisWeekEnd =
    MAX ( 'Table'[Week Ending] )
VAR LastWeekEnd = ThisWeekEnd - 7
VAR ThisWeekTable1 =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Week Ending] = ThisWeekEnd
            && 'Table'[Product] = MAX ( 'Table'[Product] )
    )
VAR ThisWeekTable2 =
    SUMMARIZE ( ThisWeekTable1, [Company] )
VAR LastWeekTable1 =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Week Ending] = LastWeekEnd
            && 'Table'[Product] = MAX ( 'Table'[Product] )
    )
VAR LastWeekTable2 =
    SUMMARIZE ( LastWeekTable1, [Company] )
RETURN
    IF (
        MAX ( 'Company'[Company] ) IN ThisWeekTable2
            && NOT ( MAX ( 'Company'[Company] ) IN LastWeekTable2 ),
        1,
        IF (
            NOT ( MAX ( 'Company'[Company] ) IN ThisWeekTable2 )
                && MAX ( 'Company'[Company] ) IN LastWeekTable2,
            2
        )
    )

 

4. Create visuals.

visuals3.PNGvisual2.PNG

 

5. Test.

company.gif

For more details, please check the attached PBIX file.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Try this:

 

1. Create measures.

Distinct This Week = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Company] ),
    FILTER ( 'Table', 'Table'[Week Ending] = MAX ( 'Table'[Week Ending] ) )
)
Distinct Last Week = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Company] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Product] ),
        'Table'[Week Ending]
            = MAX ( 'Table'[Week Ending] ) - 7
    )
)
Count of Company = COUNT( 'Table'[Company] )

 

2. Create a Company table.

Company = VALUES( 'Table'[Company] )

 

3. Create a Measure.

Measure = 
VAR ThisWeekEnd =
    MAX ( 'Table'[Week Ending] )
VAR LastWeekEnd = ThisWeekEnd - 7
VAR ThisWeekTable1 =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Week Ending] = ThisWeekEnd
            && 'Table'[Product] = MAX ( 'Table'[Product] )
    )
VAR ThisWeekTable2 =
    SUMMARIZE ( ThisWeekTable1, [Company] )
VAR LastWeekTable1 =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Week Ending] = LastWeekEnd
            && 'Table'[Product] = MAX ( 'Table'[Product] )
    )
VAR LastWeekTable2 =
    SUMMARIZE ( LastWeekTable1, [Company] )
RETURN
    IF (
        MAX ( 'Company'[Company] ) IN ThisWeekTable2
            && NOT ( MAX ( 'Company'[Company] ) IN LastWeekTable2 ),
        1,
        IF (
            NOT ( MAX ( 'Company'[Company] ) IN ThisWeekTable2 )
                && MAX ( 'Company'[Company] ) IN LastWeekTable2,
            2
        )
    )

 

4. Create visuals.

visuals3.PNGvisual2.PNG

 

5. Test.

company.gif

For more details, please check the attached PBIX file.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi @Icey 

This is absolutely amazing, and really well explained!

I just had one last question - when no product is selected in the line chart, is there a way for the "new this week" and "dropped this week" tables to show the new/dropped companies irrespective of product?

I'm not sure how you embeded the video in your last response, but see the image below (I changed one of the records in the raw data to make this more obvious):

2020-04-22 13_52_27-Interact with line chart to analyse increase_decrease over weeks - Power BI Desk.png

As you can see above, Company A has sales for product 2 in week ending 12th April, and sales for product 1 in week ending 19th April. Is there a way, when no selection is made on the line chart, that the default position for the tables on the right show the absolute change of company uptake, regardless of product?

i.e. in this example, with no selection made on the chart on the left, the tables should both be blank as Company A still has sales for at least 1 product.

Sorry for the further questions and I really appreciate your assistance!

Aaron

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Change the Measure like so:

Measure = 
VAR ThisProduct =
    VALUES ( 'Table'[Product] )               ----------Added.
VAR ThisWeekEnd =
    MAX ( 'Table'[Week Ending] )
VAR LastWeekEnd = ThisWeekEnd - 7
VAR ThisWeekTable1 =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Week Ending] = ThisWeekEnd
            && 'Table'[Product] IN ThisProduct  --------Changed.
    )
VAR ThisWeekTable2 =
    SUMMARIZE ( ThisWeekTable1, [Company] )
VAR LastWeekTable1 =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Week Ending] = LastWeekEnd
            && 'Table'[Product] IN ThisProduct  --------Changed.
    )
VAR LastWeekTable2 =
    SUMMARIZE ( LastWeekTable1, [Company] )
RETURN
    IF (
        MAX ( 'Company'[Company] ) IN ThisWeekTable2
            && NOT ( MAX ( 'Company'[Company] ) IN LastWeekTable2 ),
        1,
        IF (
            NOT ( MAX ( 'Company'[Company] ) IN ThisWeekTable2 )
                && MAX ( 'Company'[Company] ) IN LastWeekTable2,
            2
        )
    )

product2.gif

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi Icey,

This is awesome - thanks so much for all your help!

Aaron

 

amitchandak
Super User
Super User

@Anonymous ,

Please check this file which uses week rank to deal with this week last week.

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

Anonymous
Not applicable

Hi @amitchandak 

I appreciate you taking the time to respond but this doesn't help me at all. I'm not sure if you have understood what I'm trying to do.

Aaron

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.