Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

How to sum specific rows based on slicer

dear all hi, i would like your support for an issue that i have in a chart where i present rates (basically it is the formula: cost divided to production volumes) per month. I have a column (in ta...

Need Help

v-kelly-msft

4 years ago

Hi Anonymous ,

Create 2 measures as below:

Measure2 =
VAR vol36 =
    CALCULATE (
        SUM ( Volumn2[Quantity] ),
        FILTER (
            ALL ( Volumn2 ),
            'Volumn2'[Produced Product ] = "36"
                && 'Volumn2'[Month] = MAX ( 'cost2'[Month] )
        )
    )
VAR volME =
    CALCULATE (
        SUM ( Volumn2[Quantity] ),
        FILTER (
            ALL ( Volumn2 ),
            'Volumn2'[Produced Product ] = "ME"
                && 'Volumn2'[Month] = MAX ( 'cost2'[Month] )
        )
    )
VAR departA =
    CALCULATE (
        SUM ( 'cost2'[Amount] ),
        FILTER (
            ALL ( cost2 ),
            'cost2'[Department ] = "department A"
                && 'cost2'[Month] = MAX ( 'cost2'[Month] )
        )
    )
VAR departB =
    CALCULATE (
        SUM ( 'cost2'[Amount] ),
        FILTER (
            ALL ( cost2 ),
            'cost2'[Department ] = "department B"
                && 'cost2'[Month] = MAX ( 'cost2'[Month] )
        )
    )
VAR departC =
    CALCULATE (
        SUM ( 'cost2'[Amount] ),
        FILTER (
            ALL ( cost2 ),
            'cost2'[Department ] = "department C"
                && 'cost2'[Month] = MAX ( 'cost2'[Month] )
        )
    )
VAR _departA =
    DIVIDE ( departA, vol36 )
VAR _departB =
    DIVIDE ( departB, volME )
VAR _departC =
    DIVIDE ( departC, volME )
VAR _total =
    IF ( "department A" IN FILTERS ( 'Table'[department] ), _departA, 0 )
        + IF ( "department B" IN FILTERS ( 'Table'[department] ), _departB, 0 )
        + IF ( "department C" IN FILTERS ( 'Table'[department] ), _departC, 0 )
RETURN
    IF (
        ISINSCOPE ( 'Table'[department] ),
        SWITCH (
            SELECTEDVALUE ( 'Table'[department] ),
            "department A", _departA,
            "department B", _departB,
            "department C", _departC
        ),
        _total
    )

Measure3 =
SUMX ( VALUES ( 'cost2'[Month] ), 'Table'[Measure 2] )

And you will see:

For the updated .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

How to sum specific rows based on slicer (1).pbix42 KB

v-kelly-msft

Community Support

4 years ago

Hi Anonymous ,

Create a dim table as below:

Then create a measure as below:

Measure =
VAR vol36 =
    CALCULATE (
        SUM ( Volumes[36 ] ),
        FILTER ( ALL ( Volumes ), 'Volumes'[Month ] = MAX ( 'costs'[Month ] ) )
    )
VAR volME =
    CALCULATE (
        SUM ( 'Volumes'[ME] ),
        FILTER ( ALL ( Volumes ), 'Volumes'[Month ] = MAX ( 'costs'[Month ] ) )
    )
VAR _departA =
    DIVIDE ( SUM ( costs[ department A ] ), vol36 )
VAR _departB =
    DIVIDE ( SUM ( 'costs'[department B ] ), volME )
VAR _departC =
    DIVIDE ( SUM ( 'costs'[department C] ), volME )
VAR _total =
    IF ( "department A" IN FILTERS ( 'Table'[Rate] ), _departA, 0 )
        + IF ( "department B" IN FILTERS ( 'Table'[Rate] ), _departB, 0 )
        + IF ( "department C" IN FILTERS ( 'Table'[Rate] ), _departC, 0 )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Rate] ),
        SWITCH (
            SELECTEDVALUE ( 'Table'[Rate] ),
            "department A", _departA,
            "department B", _departB,
            "department C", _departC
        ),
        _total
    )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

How to sum specific rows based on slicer.pbix37 KB

Anonymous

Not applicable

4 years ago

Dear kelly,

million thanks for this great feedback and input, Please accept my apologies for not defining correclty the format of my tables. I shared a simplified format so that it can be more clear.

The correct format of my tables are as per below:

dim table

Departments

department A

department B

department C

Table with costs

Month	Department	Amount
10	department A	4,78 €
10	department B	426,68 €
10	department C	568,81 €
11	department A	324,06 €
11	department B	720,41 €
11	department C	18,30 €
12	department A	21,82 €
12	department B	100,37 €
12	department C	378,00 €

Table with Volumes

Month	Produced Product	Quantity
10	36	460
10	ME	126
11	36	856
11	ME	50
12	36	170
12	ME	80

On top i have already created a slicer in order to select department and a stacked column chart to display the results.

So when i select in slicer "Departement A" the stacked column chart should represent rates of A per month. when i select in slicer "Departement B" the stacked column chart should represent rates of B per month etc.

I hope you can modify this great solution that you already shared with minimum impact on your time 😞

Thank you very much once again for the support!

Panagiotis

v-kelly-msft

Community Support

4 years ago

Hi Anonymous ,

Create 2 measures as below:

Measure2 =
VAR vol36 =
    CALCULATE (
        SUM ( Volumn2[Quantity] ),
        FILTER (
            ALL ( Volumn2 ),
            'Volumn2'[Produced Product ] = "36"
                && 'Volumn2'[Month] = MAX ( 'cost2'[Month] )
        )
    )
VAR volME =
    CALCULATE (
        SUM ( Volumn2[Quantity] ),
        FILTER (
            ALL ( Volumn2 ),
            'Volumn2'[Produced Product ] = "ME"
                && 'Volumn2'[Month] = MAX ( 'cost2'[Month] )
        )
    )
VAR departA =
    CALCULATE (
        SUM ( 'cost2'[Amount] ),
        FILTER (
            ALL ( cost2 ),
            'cost2'[Department ] = "department A"
                && 'cost2'[Month] = MAX ( 'cost2'[Month] )
        )
    )
VAR departB =
    CALCULATE (
        SUM ( 'cost2'[Amount] ),
        FILTER (
            ALL ( cost2 ),
            'cost2'[Department ] = "department B"
                && 'cost2'[Month] = MAX ( 'cost2'[Month] )
        )
    )
VAR departC =
    CALCULATE (
        SUM ( 'cost2'[Amount] ),
        FILTER (
            ALL ( cost2 ),
            'cost2'[Department ] = "department C"
                && 'cost2'[Month] = MAX ( 'cost2'[Month] )
        )
    )
VAR _departA =
    DIVIDE ( departA, vol36 )
VAR _departB =
    DIVIDE ( departB, volME )
VAR _departC =
    DIVIDE ( departC, volME )
VAR _total =
    IF ( "department A" IN FILTERS ( 'Table'[department] ), _departA, 0 )
        + IF ( "department B" IN FILTERS ( 'Table'[department] ), _departB, 0 )
        + IF ( "department C" IN FILTERS ( 'Table'[department] ), _departC, 0 )
RETURN
    IF (
        ISINSCOPE ( 'Table'[department] ),
        SWITCH (
            SELECTEDVALUE ( 'Table'[department] ),
            "department A", _departA,
            "department B", _departB,
            "department C", _departC
        ),
        _total
    )

Measure3 =
SUMX ( VALUES ( 'cost2'[Month] ), 'Table'[Measure 2] )

And you will see:

For the updated .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

How to sum specific rows based on slicer (1).pbix42 KB

Forum Discussion

How to sum specific rows based on slicer

Related content

Clear Specific Slicers

Specific values in a slicer

Sorting X- axis based on Month-Year slicer selection

Dynamic Buckets based on slicer

Specific matrix