Forum Discussion

carlenb's avatar
carlenb
Icon for Advocate II rankAdvocate II
3 years ago
Solved

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 attached

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

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    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's avatar
      carlenb
      Icon for Advocate II rankAdvocate II

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

  • 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.