cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## 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

1 ACCEPTED SOLUTION
Community Support

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

6 REPLIES 6
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!

Community Support

Could you pls provide some sample data for test?

Best Regards,
Kelly

Regular Visitor

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 Ratedepartment A Ratedepartment B Ratedepartment 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

Community Support

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

Regular Visitor

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

Community Support

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors