Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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

 

panagiotis_sim_3-1636099924828.png

 

  

panagiotis_sim_4-1636099942576.png

 

 

  

panagiotis_sim_5-1636099957388.png

 

Thank you for your support.

1 ACCEPTED SOLUTION

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:

vkellymsft_0-1636516727213.pngvkellymsft_1-1636516738529.png

For the updated .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

6 REPLIES 6
YukiK
Impactful Individual
Impactful Individual

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

 

Hope this helps!

v-kelly-msft
Community Support
Community Support

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
Not applicable

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

Hi  @Anonymous ,

 

Create  a dim table as below:

vkellymsft_0-1636425211721.png

 

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:

vkellymsft_1-1636425694879.pngvkellymsft_2-1636425707933.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Anonymous
Not applicable

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

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:

vkellymsft_0-1636516727213.pngvkellymsft_1-1636516738529.png

For the updated .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.