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 am having a bit of trouble with to referencing the top ranking Sales Category. I have created a measure which produces the ranking of categories based off sales:
I want to create a measure which will tell us what is the top ranking Category (SALOON AND CAB), and another measure which shows the Sales Amount for that Category.
Any tips?
Solved! Go to Solution.
Try these measures
Category Top =
VAR _A =
FILTER (
ADDCOLUMNS (
'Sales',
"Rank",
RANKX (
ALLSELECTED ( 'Sales'[Category] ),
CALCULATE ( SUM ( 'Sales'[Sales Amount] ) ),
,
DESC,
DENSE
)
),
[Rank] = 1
)
RETURN
MAXX ( _A, [Category] )
and
Sales Amount Top =
VAR _A =
FILTER (
ADDCOLUMNS (
'Sales',
"Rank",
RANKX (
ALLSELECTED ( 'Sales'[Category] ),
CALCULATE ( SUM ( 'Sales'[Sales Amount] ) ),
,
DESC,
DENSE
)
),
[Rank] = 1
)
RETURN
SUMX( _A, [Sales Amount] )
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
You are welcome.
Change that ALLSELECTED ( 'Sales'[Category] ) to 'Sales' and check it then.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hello VahidDM,
Thank you for solving this.
Quick question, I am trying to filter the ranking for a certain period (say within the last 7 days). To do this, I am replacing your line of:
CALCULATE ( SUM ( 'Sales'[Sales Amount] ))
with
CALCULATE ( SUM ( 'Sales'[Sales Amount] ), DATESINPERIOD('Sales'[DATE], TODAY(), -7, DAY)
The previously mentioned [RankSales] still shows 'Saloon and Cab' as the top rank but the measure doesn't seem to update the category as 'Saloon and Cab'.
Any thoughts?
Thanks again for your time and efforts.
Any thoughts? Thank you for your time and efforts.
You are welcome.
Change that ALLSELECTED ( 'Sales'[Category] ) to 'Sales' and check it then.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Try these measures
Category Top =
VAR _A =
FILTER (
ADDCOLUMNS (
'Sales',
"Rank",
RANKX (
ALLSELECTED ( 'Sales'[Category] ),
CALCULATE ( SUM ( 'Sales'[Sales Amount] ) ),
,
DESC,
DENSE
)
),
[Rank] = 1
)
RETURN
MAXX ( _A, [Category] )
and
Sales Amount Top =
VAR _A =
FILTER (
ADDCOLUMNS (
'Sales',
"Rank",
RANKX (
ALLSELECTED ( 'Sales'[Category] ),
CALCULATE ( SUM ( 'Sales'[Sales Amount] ) ),
,
DESC,
DENSE
)
),
[Rank] = 1
)
RETURN
SUMX( _A, [Sales Amount] )
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
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 |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |