cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## cumulative sum using related lookup table

Hello,

I would like help using a related table to help me find a cumulative sum.

I have a fact table fGALT which has various columns including  fGALT[Flange Qty]  which is a whole number and fGALT[Size] which is a pipe size in text format such as 1", 1-1/2", 2" ...

I have a related table dPipeSize which is primarily used as slicer.  dPipeSize has a unique list of pipe sizes (e.g. 1", 1-1/2", 2" ...) and a corresponding size code column as an integer 1, 2, 3, 4, ect.....   dPipeSize table is related to the fact table using a 1 to many relationship size (e.g. 1", 1-1/2", 2" ...).  The size code allows me to sort the inch based sizes nicely.

I would like to write a DAX measure that allows me to sum up all the cumulative number of flanges <= a given size on a table.  If I were to flatten the fact table it is easy, but I can't seem to do it with the related table.  If I flatten the fact table ( look up the size code for each pipe size I can write a measures as follows.

```Flanges Tested :=
SUM ( fGALT[Flange Quantity] )

```

Then for the cumulative quantity on the flatted table I would write:

```cumulative Flange Tested :=
CALCULATE (
[Flanges Tested],
FILTER ( ALLSELECTED ( fGALT ), fGALT[Size Code] <= MAX ( fGALT[Size Code] ) )
)```

If I do not flatten the table, and instead wanted to look up the size by size code on the related table, what is the proper syntax?  Here is what I tried and failed with.

```cumulative Flange Tested :=
CALCULATE (
[Flanges Tested],
FILTER (
ALLSELECTED ( fGALT ),
RELATED ( fGALT[Size Code] ) <= MAX ( RELATED ( fGALT[Size Code] ) )
)
)```

I got an error message on that measure.  Any ideas on how I can correct that?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: cumulative sum using related lookup table

You may refer to the DAX below.

```cumulative Flange Tested =
VAR s =
MAX ( dPipeSize[Size Code] )
RETURN
CALCULATE (
[Flanges Tested],
FILTER ( ALLSELECTED ( fGALT ), RELATED ( dPipeSize[Size Code] ) <= s )
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team

## Re: cumulative sum using related lookup table

You may refer to the DAX below.

```cumulative Flange Tested =
VAR s =
MAX ( dPipeSize[Size Code] )
RETURN
CALCULATE (
[Flanges Tested],
FILTER ( ALLSELECTED ( fGALT ), RELATED ( dPipeSize[Size Code] ) <= s )
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: cumulative sum using related lookup table

Looks like I didn't need the related function at all.  My solution was filtering on the related dimension table instead.

```cumulative Flange Tested :=
CALCULATE (
[Flanges Tested],
FILTER (
ALL ( PipeSize ),
RELATED ( PipeSize[Size Code] ) <= MAX ( RELATED ( PipeSize[Size Code] ) )
)
)```

Thank you for the help.