cancel
Showing results for
Did you mean:
Helper I

## Measure that sums the final result for each element filtered

Hi everyone,

I am having a doubt:
I created a Measure that works perfectly per each element in my table.
The problem appears when in Power BI, I select more than one element in my filter. The Measures sums the values of the elements I selected and then it makes the calculations. What I need is to calculate the Measure to each element in the filter and then to sum the final results.

Any opinion, Thanks

3 REPLIES 3
Community Champion

@MariaFlorenciaB

Please, Post some sample data and the expected result

Lima - Peru
Helper I

Hi @Vvelarde here goes the example:

 Product Number Product Category Year Date Sales Amount Units Sales A CAT XX 2016 31/01/2016 100.000 20.000 B CAT XX 2016 29/02/2016 50.000 25.000 C CAT XX 2016 31/03/2016 200.000 10.000 D CAT XY 2016 30/04/2016 10.000 600 E CAT XY 2016 31/05/2016 350.000 19.000 F CAT XZ 2016 30/06/2016 75.000 8.000 G CAT XZ 2016 31/07/2016 5.000 200 H CAT XZ 2016 31/08/2016 1.000.000 45.000 I CAT ZZ 2016 30/09/2016 150.000 20.000 A CAT XX 2015 31/01/2015 85.000 18.400 B CAT XX 2015 28/02/2015 42.500 23.000 C CAT XX 2015 31/03/2015 170.000 9.200 D CAT XY 2015 30/04/2015 8.500 552 E CAT XY 2015 31/05/2015 297.500 17.480 F CAT XZ 2015 30/06/2015 63.750 7.360 G CAT XZ 2015 31/07/2015 4.250 184 H CAT XZ 2015 31/08/2015 850.000 41.400 I CAT ZZ 2015 30/09/2015 127.500 18.400

We need to calculate the Price Growth versus Prior Year.

To obtain the final result we need to compare the Total Sales Current Year/ Total Deflated Sales.

The deflated sales measure is the result of: Units Sales Current year * Price/unit Prior Year (This calculation is perform for each product and the deflated is the sum of each results,)

 Deflated Correct (individual) A 92.391,30 B 46.195,65 C 184.782,61 D 9.239,13 E 323.369,57 F 69.293,48 G 4.619,57 H 923.913,04 Total deflated: 1.653.804,35

THIS CALCULATION IS AT PRODUCT LEVEL, ,so, Total deflated sales is the sum of the result for each product.

The problem appears when we use filter for product category hierarchy, the formula first group the data for the product that belongs to a category and after that complete the calculation:

 Total Sales Amount 2016 1940000 Total Sales 2015 1649000 Total units 2016 147800 Total units 2015 135976

Deflated Incorrect= 147800*(1649000/135976)=1792391

thanks!!

Microsoft

@MariaFlorenciaB

According to your description, you should use SUMX function which returns the sum of an expression evaluated for each row in a table to calculate the Total Sales instead of SUM function. The formula below is for your reference.

```Total Sales =
SUMX (
YourSalesTableName,
YourSalesTableName[Sales Amount] * YourSalesTableName[Units Sales]
)```

Regards

Announcements