How to sum specific rows based on slicer

[panagiotis_sim]

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.

[YukiK]

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]

Hi @panagiotis_sim ，

Could you pls provide some sample data for test?

Best Regards,
Kelly

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

[panagiotis_sim]

Hello Kelly,

please find below table for test:

Table with costs

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

Table with Volumes

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

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

Month	Rate department A	Rate department B	Rate department C
10	0,010	3,395	4,525
11	0,379	14,408	0,366
12	0,128	1,255	4,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]

Hi  @panagiotis_sim ,

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!

[panagiotis_sim]

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 · Accepted solution]

Hi @panagiotis_sim ,

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!