Forum Discussion

BIlix's avatar
BIlix
Icon for Helper II rankHelper II
3 years ago
Solved

Calculate Values based on a Distribution Key in a linked Table

Hello Community,

I want to Calculate Values from my Fact Table based on another Table.

I have Story Points in my Fact Table. I already have a Measure to Calculate the StoryPoints i need. 

THe Fact Table is linked many to one to A Project Dimension. The Project Dimension is linked one to many to a Distribution Dimension via ProjectID
Every ProjectID has one or multiple Desks assigned in the DIstribution Dimension and also a Ditribution/allocatiion KEy.
The final calculation should exemplary be like this:
PRoject 1 is linked to Desk 1, Desk 2, Desk 3. Desk 1 has a 0.2, Desk 2 has 0.2, Desk 3 has 0.6 Allocation

Exemplarily Project 1 has 10 assigned Story Points. The Storypoints should be multiplied by the Desk Distribution Key. Desk 1 has 2 SP, Desk 2 has 2 SP, Desk 3 has 6 SP.

My Current Measure
SUMX(Values(DimDeskDistribution[Desk]), [Storypoint Measure] * Calculate(max(DimDeskdistribution[DeskDistribution])))


When i just put Projects in a MAtrix visual, the Storypoints are distributed correctly and the Total value is correct.
But when i just put Desks in the visual, the results are wrong. For example there are 2 Desks where the linked Project has a distribution of 1. These desks have a value of 574 (total amount of story points for my data). But they should only have the values of the storypoints that were assigned to the project. Also in both Variants it shows the a total amont of 2870 SP, which is wrong(

 

 

 

  • johnt75's avatar
    johnt75
    3 years ago

    Try

    Current Measure =
SUMX (
    VALUES ( DimDeskDistribution[Desk] ),
    VAR CurrentProject =
        SELECTEDVALUE ( DimDeskDistribution[Project ID] )
    VAR Result =
        CALCULATE (
            [Storypoint Measure],
            TREATAS ( { CurrentProject }, DimProject[Project ID] )
        )
            * CALCULATE ( MAX ( DimDeskdistribution[DeskDistribution] ) )
    RETURN
        Result
)

17 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try

    Current Measure =
SUMX (
    VALUES ( DimDeskDistribution[Desk] ),
    VAR CurrentProject =
        SELECTEDVALUE ( DimDeskDistribution[Project ID] )
    VAR Result =
        CALCULATE (
            [Storypoint Measure],
            TREATAS ( { CurrentProject }, DimProject[Project ID] )
        )
            * CALCULATE ( MAX ( DimDeskdistribution[DeskDistribution] ) )
    RETURN
        Result
)
  • BIlix's avatar
    BIlix
    Icon for Helper II rankHelper II
    3 years ago

    Thanks for your reply! 

    Its working great, in a matrix Visual I can breakdown the Projects to the Desks. Also the allocation of the Storypoints is correct. 
    Could you explain how the measure can be adjusted to also show proper results in a Visual where only the Desk Column is included? I also need a visual where The desks and their allocated storypoints are included.

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      I think you could amend it to

      Current Measure =
SUMX (
    VALUES ( DimDeskDistribution[Desk] ),
    VAR CurrentProject =
        RELATED ( DimDeskDistribution[Project ID] )
    VAR Result =
        CALCULATE (
            [Storypoint Measure],
            TREATAS ( { CurrentProject }, DimProject[Project ID] )
        )
            * CALCULATE ( MAX ( DimDeskdistribution[DeskDistribution] ) )
    RETURN
        Result
)

      That should work in all scenarios.

      • BIlix's avatar
        BIlix
        Icon for Helper II rankHelper II
        3 years ago

        Unfortunately it does not work. 

        At 'RELATED' I get an error that the column does not exist or does not have any relationship to any table in the context