cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nuci Frequent Visitor
Frequent Visitor

Get the historical curve of the Top 1 brand this month

Hi there,

 

I am working on a social listening project, and one of the specific tasks is to display the Top 1 mentioned brand of THIS month and the historical curve of the brand. Probably like this

I have a date slicer working on them, the problem is that I do not know how to achieve this 2-step filtering:

1. Let date filter the Top brand

2. Let the brand filter the curve

The date cannot work directly on the curve or else only a dot will be displayed.

Please let me know if anyone has a brilliant idea to solve this!

1 ACCEPTED SOLUTION

Accepted Solutions
dearwatson Member
Member

Re: Get the historical curve of the Top 1 brand this month

Hi nuci ,

A way to think about the first measure is a filtered table (lets call it top_brand) which is a summary of months, with a calculated column [top brand] which is the top brand for that month. 

 

I am assuming we start with something like this:

brand1.PNG

To get the top brand for each month we can calculate this with some tricks:

Count of Mentions = SUM('Brand Mentions'[Mentions])
Top Brand Mentions = MAXX(VALUES('Brand Mentions'[Brand]),[Count of Mentions])
Top Brand =
CALCULATE (
VALUES ( 'Brand Mentions'[Brand] ),
TOPN (
1,
FILTER (
VALUES ( 'Brand Mentions'[Brand] ),
[Count of Mentions]
= CALCULATE ( [Top Brand Mentions], VALUES ( 'Brand Mentions'[Brand] ) )
),
'Brand Mentions'[Brand]
)
)

this will give you this output:

brand 3.PNG

 

then you can create a calculated table for the above output with this code:

Top Brand = ADDCOLUMNS(VALUES('Brand Mentions'[Month]),"Top Brand",[Top Brand])

 

Then you can model it back into the model with a cross filter as per below

brand2.PNG

then when you select a month in the "Top brand" table the top brand will be displayed and will go on to filter the brand mentions table for history.

brand 4.PNG

its a crude example but hopefully it gives you an idea of how to approach it.. gets a bit tricky..

 

heres a link to the pbix:

brand.pbix

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

3 REPLIES 3
dearwatson Member
Member

Re: Get the historical curve of the Top 1 brand this month

Hi nuci ,

A way to think about the first measure is a filtered table (lets call it top_brand) which is a summary of months, with a calculated column [top brand] which is the top brand for that month. 

 

I am assuming we start with something like this:

brand1.PNG

To get the top brand for each month we can calculate this with some tricks:

Count of Mentions = SUM('Brand Mentions'[Mentions])
Top Brand Mentions = MAXX(VALUES('Brand Mentions'[Brand]),[Count of Mentions])
Top Brand =
CALCULATE (
VALUES ( 'Brand Mentions'[Brand] ),
TOPN (
1,
FILTER (
VALUES ( 'Brand Mentions'[Brand] ),
[Count of Mentions]
= CALCULATE ( [Top Brand Mentions], VALUES ( 'Brand Mentions'[Brand] ) )
),
'Brand Mentions'[Brand]
)
)

this will give you this output:

brand 3.PNG

 

then you can create a calculated table for the above output with this code:

Top Brand = ADDCOLUMNS(VALUES('Brand Mentions'[Month]),"Top Brand",[Top Brand])

 

Then you can model it back into the model with a cross filter as per below

brand2.PNG

then when you select a month in the "Top brand" table the top brand will be displayed and will go on to filter the brand mentions table for history.

brand 4.PNG

its a crude example but hopefully it gives you an idea of how to approach it.. gets a bit tricky..

 

heres a link to the pbix:

brand.pbix

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

nuci Frequent Visitor
Frequent Visitor

Re: Get the historical curve of the Top 1 brand this month

Hi @dearwatson  - This looks extremely helpful! Let me check this out first. Thank you so much expert 😉

Highlighted
Community Support Team
Community Support Team

Re: Get the historical curve of the Top 1 brand this month

Hi @nuci ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample with table format so that we could have a test on it.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors