Forum Discussion

smitpau's avatar
smitpau
Helper I
5 years ago
Solved

Calculate Measure Help

Hi,

 

In the attached simplified example below, I'd like to create a measure which will always sum the first three row values in the table.

 

The current measure I've written is a calculate measure but uses the expression as the Company 1 values. This means it's specific to Company 1 so for Company 2 I'd have to duplicate it.

 

The new measure would ideally not require a specific company to be used in the measure expression to calculate the first three rows.

 

https://1drv.ms/u/s!Ajcv_9xvRfBd5ztQeRbRnFa5h1Am?e=mO0Ncf

 

Hope that makes sense.

 

Thanks for reading.

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, smitpau 

     

    Based on your description, you may create a measure as below. The pbix file is attached in the end.

    Sum First 3 = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Company],
        "Result",
        SWITCH(
            [Company],
            "Company1",
            CALCULATE(
                SUM('Company 1'[Value]),
                FILTER(
                    ALL('Company 1'),
                    VALUE([SI])<4
                )
            ),
            "Company2",
            CALCULATE(
                SUM('Company 2'[Value]),
                FILTER(
                    ALL('Company 2'),
                    VALUE([SI])<4
                )
            )
        )
    ),
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    For each company sum up the values where [SI] is between 1 and 3.

     

    Why do you have separate tables per company? You shoud combine the tables into one, either via DAX UNION() or better in Power Query.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, smitpau 

     

    Based on your description, you may create a measure as below. The pbix file is attached in the end.

    Sum First 3 = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Company],
        "Result",
        SWITCH(
            [Company],
            "Company1",
            CALCULATE(
                SUM('Company 1'[Value]),
                FILTER(
                    ALL('Company 1'),
                    VALUE([SI])<4
                )
            ),
            "Company2",
            CALCULATE(
                SUM('Company 2'[Value]),
                FILTER(
                    ALL('Company 2'),
                    VALUE([SI])<4
                )
            )
        )
    ),
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.