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.
Hi,
I have the following table of total sold by Week.
I am trying to extract just the Week of the best selling (max total sold from Year to selected date) so that I can put it onto a single value card.
I can get the value of the best total sales week with this formula
I would appreciate it if somebody can help me to retrieve only the Week corresponding to the Best week sales.Thanks
Year YearWeek Week Total sold
2021 | 2021-34 | 34 | 1 |
2021 | 2021-33 | 33 | 1 |
2021 | 2021-31 | 31 | 1 |
2021 | 2021-27 | 27 | 3 |
2021 | 2021-26 | 26 | 3 |
2021 | 2021-25 | 25 | 3 |
2021 | 2021-24 | 24 | 48 |
2021 | 2021-23 | 23 | 238 |
2021 | 2021-22 | 22 | 310 |
2021 | 2021-21 | 21 | 331 |
2021 | 2021-20 | 20 | 245 |
2021 | 2021-19 | 19 | 249 |
2021 | 2021-18 | 18 | 281 |
2021 | 2021-17 | 17 | 219 |
2021 | 2021-16 | 16 | 283 |
2021 | 2021-15 | 15 | 254 |
2021 | 2021-14 | 14 | 294 |
2021 | 2021-13 | 13 | 306 |
2021 | 2021-12 | 12 | 288 |
2021 | 2021-11 | 11 | 249 |
2021 | 2021-10 | 10 | 237 |
2021 | 2021-09 | 9 | 283 |
2021 | 2021-08 | 8 | 229 |
2021 | 2021-07 | 7 | 308 |
2021 | 2021-06 | 6 | 239 |
2021 | 2021-05 | 5 | 300 |
2021 | 2021-04 | 4 | 237 |
2021 | 2021-03 | 3 | 206 |
2021 | 2021-02 | 2 | 157 |
Solved! Go to Solution.
Hi @Anonymous , try this:
Highest Week =
var _maxSold= MAX(myTable[Total Sold]) // get the highest weekly sold
var _calc =CALCULATE(MAX(myTable[Week]),myTable[Total Sold]=_maxSold)
return _calc
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hi,
Try these measures:
Total sales = SUM(Data[Total sold])
Best selling week = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Week] ), [Total Sales] ), 1 )
Another way to achieve this...
Assuming the name of your table is Sales then try this...
Week with highest sale =
CONCATENATEX (
TOPN (
1,
Sales,
Sales[Total sold]
),
Sales[Week],
", ",
Sales[Total sold], DESC
)
Another way to achieve this...
Assuming the name of your table is Sales then try this...
Week with highest sale =
CONCATENATEX (
TOPN (
1,
Sales,
Sales[Total sold]
),
Sales[Week],
", ",
Sales[Total sold], DESC
)
Hi,
Try these measures:
Total sales = SUM(Data[Total sold])
Best selling week = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Week] ), [Total Sales] ), 1 )
Hi @Anonymous , try this:
Highest Week =
var _maxSold= MAX(myTable[Total Sold]) // get the highest weekly sold
var _calc =CALCULATE(MAX(myTable[Week]),myTable[Total Sold]=_maxSold)
return _calc
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |