Measure that sums the final result for each element filtered

[MariaFlorenciaB]

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

[Vvelarde]

@MariaFlorenciaB

Please, Post some sample data and the expected result 

[MariaFlorenciaB]

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

[v-ljerr-msft]

@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