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 all,
First post for me here on a problem I have for a few days.
I am working on a dashboard displaying sales data for a bunch of shops.
To be properly interpreted, the sales numbers has to be adjusted by a multiplier.
This multiplier is different for every shop and is changing through time.
The multiplier values are stored in a "Multiplier" table :
ShopID | Date | Multiplier |
1 | 01.01.2018 | 1 |
1 | 01.01.2020 | 1.2 |
2 | 01.01.2018 | 1.1 |
3 | 02.03.2019 | 1.05 |
On the other end, the sales values are stored in another "Sales"table :
ShopID | Date | SalesNb |
1 | 01.01.2018 | 545 |
2 | 01.01.2018 | 362 |
3 | 01.01.2018 | 421 |
1 | 02.01.2018 | 532 |
2 | 02.01.2018 | 350 |
(...) | (...) | (...) |
I also have a "DateTable" where I store all the date input of my data model and a "Shop" Table.
Regarding the relationships :
"DateTable" 1 ---->-----* "Sales"
"Shop" 1 ---->---- * "Sales"
"Shop" 1 ---->-----* "Multiplier"
"DateTable" 1 ---->---- * "Multiplier"
My goal is to have a measure that return for a given asset and for a given date, the multiplier that should be apply to the Sales number.
This measure will then be used in a SWITCH measure to allow the user to pick if they want the adjusted number or not.
Good luck and tell me if you want more information !
Solved! Go to Solution.
Some DAX to add it as a calculated column to Sales would be
Multiplier =
VAR _Shop = Sales[ShopID]
VAR _Date = Sales[Date]
VAR _Latest =
TOPN(
1,
FILTER(Multiplier, Multiplier[Date] <= _Date && Multiplier[ShopID] = _Shop),
Multiplier[Date], DESC
)
RETURN
MAXX(_Latest, Multiplier[Multiplier])
The measure is then relatively simple
Sales with Multiplier =
SUMX(
Sales,
Sales[SalesNb] * Sales[Multiplier]
)
Have you thought about adding the Multiplier to the Sales table, either in Power Query or the source?
From a modelling perspective that's where it should go if you're going to use it in a measure.
The resulting measure will also perform better - something to consider if you have a large amount of data.
Some DAX to add it as a calculated column to Sales would be
Multiplier =
VAR _Shop = Sales[ShopID]
VAR _Date = Sales[Date]
VAR _Latest =
TOPN(
1,
FILTER(Multiplier, Multiplier[Date] <= _Date && Multiplier[ShopID] = _Shop),
Multiplier[Date], DESC
)
RETURN
MAXX(_Latest, Multiplier[Multiplier])
The measure is then relatively simple
Sales with Multiplier =
SUMX(
Sales,
Sales[SalesNb] * Sales[Multiplier]
)
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |