Forum Discussion
How to get the relative value? Model attached
Hi,
I'm trying to wrap my head around this. I've attached the model here: https://drive.google.com/file/d/1yIC3LKIDAaCzr5LbQyg5k6w2p4poSNB3/view?usp=sharing
Also see screenshot of the model below and the expected end result.
The expected result is a bar chart where you can filter on project, supplier name and date. The Y-axis will be sales for the total number of projects and the X-axis will be dates based on project starts. Example: the value of project id 1 is 10 700, and the value of the project start in 2024-05-08 is 3 * 10 700 = 32 100. So:
- The Sales table will calculate the volume per project by multiplying Sales[Price] and Sales[Quantity]
- The additional step is that the measure must also multiply the volume from Sales with the Number of starts in the DimProjectPlan table. Meaning: the volume for Sales is only for one (1) project start, but the correct sum would be Sales[Price] * Sales[Quantity] * DimProjectPlan[Number of starts] as the same project may be sold more than one time. This is the Y-axis.
- The X-axis in the chart will be the date from DimProjectPlan[Date] as this shows when each project starts
Data
- Sales contain price, quantity, project id, project, sub-project, suppliers. Duplicates
- DimProject is a list of project id, project, sub-project. No duplicates
- DimProjectPlan is a list of project id, project, sub-project, number of projects, start date. Duplicates
- DimSupplier is a unique list of suppliers. No duplicates
- Calendar
Thanks for any help
Hi carlenb
Please refr to attachedSales Volumne = SUMX ( VALUES ( DimProject[ID] ), SUMX ( CALCULATETABLE ( Sales ), Sales[Quantity] * Sales[Price] ) * CALCULATE ( MAX ( DimProjectPlan[Number of starts] ) ) )
3 Replies
- carlenb
Advocate II
Added the following example in the thread start:
The value of project id 1 is 10 700, and the value of the project start in 2024-05-08 is 3 * 10 700 = 32 100.