I have a table with column, date, product name, product category, profit. How can I use DAX to group the data by date and rank the data by profit. I would like to return only the bottom 13th value by product category in a table?
Dear @jonatong
For grouping the data based on the date column, you can simply use a date table. I hope the blow link would be helpful.
But for ranking the data by profit, both in the table and graph, you only need to use sort option which is so simple.
I hope my notes work for you
The Best
Hossein
@jonatong Try something like this:
Table 9 =
VAR TempTable =
ADDCOLUMNS (
SUMMARIZE (
Sales,
Products[Brand],
Products[Category],
Dates[Calendar Year Number]
),
"@Sales", [Total Sales]
)
VAR SummarizeTempTable =
SELECTCOLUMNS ( SUMMARIZE ( TempTable, [@Sales] ), "SummarizedSales", [@Sales] )
VAR Result =
ADDCOLUMNS (
TempTable,
"Ranking", COUNTROWS ( FILTER ( SummarizeTempTable, [SummarizedSales] <= [@Sales] ) )
)
VAR Bottom13Value =
COUNTROWS ( Result ) - 13
VAR ThatValue =
FILTER ( Result, [Ranking] = Bottom13Value )
RETURN
ThatValue
4 Likes
Attaching light version of the model in case if you want to play with it:
Contoso.pbix (3.0 MB)
Thanks, I think I did not explain clearly about the ranking. The ranking on the profit is done within each Category, P1 and date. I want to get the bottom 13th value.
Example,
Category:Cat1, Product: P1
20200927, Profit = $100, Rank 1
20200926, Profit = $80, Rank 2
…
Category:Cat2, Product: P2
20200927, Profit = $110, Rank 1
20200926, Profit = $95, Rank 2
…
Result:
Category: Cat1, Product: P1, Profit : <bottom 13th value>
Category: Cat1, Product: P2, Profit : <bottom 13th value>
…
I think a mock-up in Excel of your goal output would help.
Below is the raw data
Below is the result I hope to get which I am using Pivot table and formula small to get the 3th smallest value.(this example is bottom 3rd value)
Hope this is clearer. Thanks
Hi @jonatong.
I’m still a bit confused, as the title of your initial post says you want the 13th last value, yet your latest post description and data screenshot is looking for the 3rd last value, so I had to pick one and picked the 3rd last.
As with most things in Power BI, there are many ways to do the same thing … here’s one way to identify the profit value for the third last date for a product using a number of intermediate measures:
xCurrent Date = SELECTEDVALUE( Products[Date] )
xCurrent Product = SELECTEDVALUE( Products[Product] )
xMax Date =
CALCULATE(
MAX( Products[Date] ),
FILTER(
ALLEXCEPT( Products, Products[Product] ),
MAX( Products[Product] ) = [xCurrent Product]
)
)
xMax Date Minus 2 =
IF( [xCurrent Date] = [xMax Date] - 2, [xCurrent Date], BLANK() )
3rd Last Profit Value =
IF( ISBLANK( [xMax Date Minus 2] ), BLANK(), MAX( Products[Profit] ) )
(I added a few rows to your sample dataset just to make sure I was retrieving the intended value.)
eDNA Forum - Get bottom 3rd value.xlsx (9.2 KB)
Hope this helps.
Greg
eDNA Forum - Get bottom 3rd value.pbix (21.4 KB)
1 Like
Hi Greg,
Thanks, I change it to 3rd bottom profit so that it is simpler to see from the example. Actually i am looking for 3rd bottom profit rather than date.
So you’re looking for the 3rd lowest profit by value then for each product? So in your sample dataset date and category should be disregarded?
Greg
Yes, 3rd lowest profit by value for each product. Date and category can be disregarded.
Hi @jonatong.
Busy day … quick-and-dirty partial solution (hopefully). I tried again using a calculated column and a measure, and this gives me the bottom 3 values (whereas I think you are looking for only the 3rd-lowest value). I got a bit stuck in trying to handle cases where there are duplicate lower values (e.g., when both the 2nd and 3rd lowest profit values are the same), but here’s my work-in-progress:
3rd Lowest Profit =
VAR _3rdLowest = IF(
MAX( Profits[Rank Profits by Product ASC] ) = 3,
MAX( Profits[Profit] ),
BLANK()
)
VAR _2ndLowest = IF(
MAX( Profits[Rank Profits by Product ASC] ) = 2,
MAX( Profits[Profit] ),
BLANK()
)
VAR _1stLowest = IF(
MAX( Profits[Rank Profits by Product ASC] ) = 1,
MAX( Profits[Profit] ),
BLANK()
)
VAR _Result = SWITCH(
TRUE(),
NOT( ISBLANK( _3rdLowest ) ), _3rdLowest,
NOT( ISBLANK( _2ndLowest ) ), _2ndLowest,
NOT( ISBLANK( _1stLowest ) ), _1stLowest,
BLANK()
)
RETURN
_Result
Hope this helps, or that it helps another forum member take it across the finish line.
Greg
2 Likes
Hi @jonatong, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Hi Greg,
This partially solve my problem. You are correct that I am looking for the 3rd lowest value. So for P1, there is no 3rd value, but 2 2nd value. Is it possible to rank it by count so that there is a 3rd lowest value? Thanks
I haven’t come across a way … I’d guess you’d need to add in an additional field to break ties so that your 2 “2nd” values could be designated as a “2nd” and a “3rd” (i.e., so they could be ranked uniquely and you’d always have a “3rd”) …
Greg
Ok, Thanks Greg! Is there anyway to sort and insert a index column using DAX?
Hi Greg
if I have a product sub type, how can I roll the profit up to Product for ranking?
Thanks
Hi @jonatong. You can use composite ranking … first by Product, then by Product Sub Type. Here’s a recent forum thread with more details.
Hope this helps.
Greg
Hi Greg,
Sorry I mean that there is sub product type which might have profit for the same date for the same product which i need to rank the daily profit by the profit…