How to get the relative value? Model attached

carlenb — Thu, 05 Oct 2023 06:39:26 GMT

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

Re: How to get the relative value? Model attached

carlenb — Thu, 05 Oct 2023 06:40:01 GMT

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.

Re: How to get the relative value? Model attached

tamerj1 — Thu, 05 Oct 2023 13:12:22 GMT

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] ) ) )

Re: How to get the relative value? Model attached

carlenb — Thu, 05 Oct 2023 14:34:09 GMT

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

topic How to get the relative value? Model attached in DAX Commands and Tips

How to get the relative value? Model attached

Re: How to get the relative value? Model attached

Re: How to get the relative value? Model attached

Re: How to get the relative value? Model attached