How to get the relative value? Model attached

[carlenb]

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

[tamerj1 · Accepted solution]

Hi @carlenb 
Please refr to attached

Sales Volumne = 
SUMX ( 
    VALUES ( DimProject[ID] ),
    SUMX ( 
        CALCULATETABLE ( Sales ),
        Sales[Quantity] * Sales[Price]
    )
        * CALCULATE ( MAX ( DimProjectPlan[Number of starts] ) )
)

[carlenb]

Many thanks @tamerj1 - I got it to work as expected 🙂 thanks for submitting the solution as well! 

[carlenb]

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.