cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate difference using a ranking column

Hello all. I have some cumulative sales data which publishes every week, and I need to figure out the delta in such a manner I can easily target the larger books/debooks. Lets say my data look like this:

 Group SubGroup Continent Country Sales Week ending Order Fruit Apples Africa Zimbabwe 100 21/07/2023 3 Fruit Apples Asia Fiji 120 14/07/2023 2 Fruit Oranges Asia Vietnam 300 7/07/2023 1 Fruit Apples Asia Fiji 250 21/07/2023 3 Veg Carrots Australasia New Zealand 10 14/07/2023 2 Veg Carrots Europe Czech Republic 150 7/07/2023 1

Subgroups are always subsets of groups, and countries are always subsets of continents. The week ending field is text and tends to get a bit freeform, so I'm generating the 'Order' column where oldest is smallest and the week ending:order relationship is 1:1. The sales number is cumulative of all prior weeks/'orders' in the dataset.

In this example we can see that in the last week we've sold 130 fijian apples, and it's this number I wish to calculate to display in a matrix that the user can drill into .

I previously asked a similar question and tried to modify that to suit my new question, but I'm missing a fundamental concept as while my totals are correct, the values at a continent/country level are preposterously large

Thanks 🙂

2 REPLIES 2
Super User

@unusdusrname , A column that can give you week sales from cumulative

sales this week=
var _date = maxx(filter( Table,[Group] = earlier([Group]) && [SubGroup] = earlier([SubGroup]) && [Continent] = earlier([Continent])
&& [Country] = earlier(Country) && [Week ending Order] < earlier([Week ending Order])),[Week ending Order])
return
[Sales] - Sumx(filter( Table,[Group] = earlier([Group]) && [SubGroup] = earlier([SubGroup]) && [Continent] = earlier([Continent])
&& [Country] = earlier(Country) && [Week ending Order] =_max ),[sales])

Measure to compare this week vs last week

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

to get the week start/week end of Friday, refer

Frequent Visitor

Thanks Amit. I didn't quite get your code to work however, PowerBi reports syntax errors.

So I expunged the surplus carriage returns, and added the square braces around [Country]. I also changed [Week Ending Order] for just [Order] as those two were different fields.

I've still got a syntax error in the return clause where [Order] = _max - am I right in thinking _max is intended to be a variable that's been defined? So it maybe ought to be _date defined in the var clause above? Here's what I tried.

``````sales this week =
var _date = maxx(filter( Table,[Group] = earlier([Group]) && [SubGroup] = earlier([SubGroup]) && [Continent] = earlier([Continent]) && [Country] = earlier([Country]) && [Order] < earlier([Order])),[Order])
return
[Sales] - Sumx(filter( Table,[Group] = earlier([Group]) && [SubGroup] = earlier([SubGroup]) && [Continent] = earlier([Continent])&& [Country] = earlier([Country]) && [Order] =_date ),[sales])``````

Just gotta love how PowerBI won't share even which line holds the supposed syntax error. Can you see what is wrong?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors