Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I have one table with daily TV GRP of different car brands of different TV channels. With built-in visualization, following table is created.
I want the top 2 channels with the highest total GRP of each car brand by month displayed horizontally like the following image:
I was thinking of creating a new table with car brand names only and then use rankx. But I can't get it to work. Could someone please help me on this? Thank you so much for your time and help.
Here is a sample pbix file.
https://drive.google.com/file/d/1GdQ72xFYuSIi9AhPntUqa2mpbgieNxdS/view?usp=sharing
Best regards,
David
Hi @ primolee ,
Here are the steps you can follow:
1. Create a calculate table.
Sum =
SUMMARIZE('Daily GRP',
[Car Brand],[Channel Group],[Month],
"Sum",SUM('Daily GRP'[GRP]))
2. Create measure.
Rank =
RANKX(
FILTER(
ALL('Sum'),
[Car Brand]=MAX([Car Brand])),
CALCULATE(MAX([Sum])),,DESC,Dense)
Channel with No.1 GRP =
CALCULATE(MAX('Sum'[Channel Group]),FILTER('Sum','Sum'[Rank]=1) )
Channel with No.2 GRP =
CALCULATE(MAX('Sum'[Channel Group]),FILTER('Sum','Sum'[Rank]=2) )
3. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@primolee , I suggested a Column Rank approch .
Create these new columns
Rank Col = rankx(filter(Table, [Car Brand] = earlier([Car Brand])),[GRP] , ,desc,dense)
Rank Desc ="Channel with GRP No." & [Rank Col] & " GRP"
Then use Rank Desc on Matrix Column and Car Brand on row and Channel (first/last) as value
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
Hello @amitchandak
Thank you so much for your prompt reply.
Sorry but maybe I did not make myself clear. My table contains daily GRP information of several months. Creating new columns like this will have ranking by daily, I want to compare by month so this matrix would not work. Moreover, I want to use drop-down filter to select multiple months so that the ranking would change dynamically by summing up the subtotal of GRP.
Following link is the sample I created.
https://drive.google.com/file/d/1GdQ72xFYuSIi9AhPntUqa2mpbgieNxdS/view?usp=sharing
I am not sure if I made myself clear enough. Please give me a hand on this, thank you!
Best regards,
David
Anyone has any idea?
Following is a sample of my pbix, please give me a hand on this, thank you.
https://drive.google.com/file/d/1GdQ72xFYuSIi9AhPntUqa2mpbgieNxdS/view?usp=sharing
Hi @ primolee ,
Here are the steps you can follow:
Rank_Day =
RANKX(
FILTER(
ALL('Table'),
'Table'[Date]=MAX('Table'[Date])),
CALCULATE(MAX('Table'[Grp])),,ASC,Dense)
2. Create a column to calculate the monthly total, create a measure to calculate the monthly ranking
Rank_Month_Mount =
RANKX(
ALL('Table'),
CALCULATE(MAX('Table'[Month_mount])),,ASC,Dense)
3. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-yangliu-msft
Thank you so much for your help. I am very sorry but it is still different from what I wanted to do. Sorry I did not explain clearly enough previously.
There are daily GRP of many different car brands in many different months. I want to find the top 2 TV Channels with the top 2 monthly-total GRP by each brand in each month. Not of a specific date in that month, but the total GRP of that month.
In the example you provided, if I select Month 9, this would be the result:
If I select Month 10, this would be the result:
Is there a way to achieve this? Thank you so much for your time and help.
Best regards,
David
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |