Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Ending | Company | Product | Sales |
19 April 2020 | Company B | Product 1 | 1000 |
19 April 2020 | Company D | Product 1 | 1500 |
19 April 2020 | Company B | Product 2 | 2000 |
19 April 2020 | Company C | Product 1 | 750 |
12 April 2020 | Company C | Product 1 | 500 |
12 April 2020 | Company D | Product 1 | 5000 |
12 April 2020 | Company B | Product 1 | 2500 |
12 April 2020 | Company A | Product 1 | 250 |
12 April 2020 | Company A | Product 2 | 900 |
12 April 2020 | Company B | Product 2 | 4000 |
05 April 2020 | Company A | Product 1 | 1500 |
05 April 2020 | Company A | Product 2 | 2000 |
05 April 2020 | Company B | Product 1 | 3000 |
My hope is that I can create something that looks roughly like this:
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
Solved! Go to Solution.
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.
5. Test.
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.
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.
5. Test.
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.
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):
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
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
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Icey,
This is awesome - thanks so much for all your help!
Aaron
@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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |