cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MariaFlorenciaB
Helper I
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
Vvelarde
Community Champion
Community Champion

@MariaFlorenciaB

 

Please, Post some sample data and the expected result 

 




Lima - Peru

Hi @Vvelarde here goes the example:

 

Product NumberProduct CategoryYearDateSales AmountUnits Sales
ACAT XX201631/01/2016            100.000          20.000
BCAT XX201629/02/2016              50.000          25.000
CCAT XX201631/03/2016            200.000          10.000
DCAT XY201630/04/2016              10.000                600
ECAT XY201631/05/2016            350.000          19.000
FCAT XZ201630/06/2016              75.000             8.000
GCAT XZ201631/07/2016                5.000                200
HCAT XZ201631/08/2016        1.000.000          45.000
ICAT ZZ201630/09/2016            150.000          20.000
ACAT XX201531/01/2015              85.000          18.400
BCAT XX201528/02/2015              42.500          23.000
CCAT XX201531/03/2015            170.000             9.200
DCAT XY201530/04/2015                8.500                552
ECAT XY201531/05/2015            297.500          17.480
FCAT XZ201530/06/2015              63.750             7.360
GCAT XZ201531/07/2015                4.250                184
HCAT XZ201531/08/2015            850.000          41.400
ICAT ZZ201530/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 Amount20161940000
Total Sales20151649000
Total units2016147800
Total units2015135976

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

 

 

thanks!!

@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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors