Forum Discussion

Anonymous's avatar
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's avatar
    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!

v-kelly-msft's avatar
v-kelly-msft
Community Support
4 years ago

Hi Anonymous ，

 

Could you pls provide some sample data for test?

 

Best Regards,
Kelly

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Kelly,

     

    please find below table for test:

     

    Table with costs

    Month  department A department B department C
    104,78 €426,68 €568,81 €
    11324,06 €720,41 €18,30 €
    1221,82 €100,37 €378,00 €

     

    Table with Volumes

    Month 36 ME
    10460126
    1185650
    1217080


    So the final Rate that i should get is the below:

     

    Month Rate
    department A     		Rate
    department B     		Rate
    department C
    100,0103,3954,525
    110,37914,4080,366
    120,1281,2554,725

     

    So we need to have the below resault:
    for department A= cost of department A/volumes of 36

    for department B= cost of department B/volumes of ME

    for department C= cost of department C/volumes of ME

     

    Thank you in advance for the support.

    Panagiotis

    • v-kelly-msft's avatar
      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!

      • Anonymous's avatar
        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

        MonthDepartment Amount
        10department A4,78 €
        10department B426,68 €
        10department C568,81 €
        11department A324,06 €
        11department B720,41 €
        11department C18,30 €
        12department A21,82 €
        12department B100,37 €
        12department C378,00 €

         

        Table with Volumes

        MonthProduced Product Quantity
        1036460
        10ME126
        1136856
        11ME50
        1236170
        12ME80

         

        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