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

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

        Try

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