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 table actual costs) with actual costs per month and another column that matches the cost with departments A,B,C.

I have a column (in table actual volumes) with actual volumes per month and another column that matches the volumes with department A,B.

Out of the 3 departments that i have (A,B,C) only 2 of them (A,B) are producing volumes. Department A is producing product 36 and department B is producing product ME. I have a slicer to select department and present rate to a chart .

What I need to do is:

  1. when I select in slicer to see Rate for department A , it should do the formula : costs of A / volumes of product 36
  2. when I select in slicer to see Rate for department B , it should do the formula : costs of B / volumes of product ME
  3. when I select in slicer to see Rate for department C , it should do the formula : costs of C / volumes of product ME (this is the hard part since department C is not producing and product but the rate should be based on volumes of product ME which is produced in department B)

Steps 1 and 2 are ok by simple formula: sum(actual costs[costs])/sum(Volumes[Quantity]). But when I select in Slicer department C the chart shows infinity since no volumes have been matched with department C.

 

Any idea how to handle this? Below and example for all above scenarios

 

 

  

 

 

  

 

Thank you for your support.

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!

6 Replies

  • 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!

  • YukiK's avatar
    YukiK
    Impactful Individual
    4 years ago

    Please consider using DIVIDE() function. That'll catch divide-by-zero error and should help not show the value of infinity.

     

    Hope this helps!