Trying to get total sum of measure correctly

[Oelshamy]

Hi, I need help in getting the correct values for the sum of a measure, the total showing wrongly. 

I have a measure that calculates the cost of consumption of electricity readings, 

the dax formula for cost is: 

Electricity = IF('Table'[Consumption]>10000,(('Table'[Consumption]-10000)*0.22+(6000*0.17)+(4000*0.13)),(IF('Table'[Consumption]<4000,('Table'[Consumption]*0.13),((4000*0.13)+('Table'[Consumption]*0.17)))))

The Total should be 40308

When i tried to sum the measure using SUMX('Table','Table'[Electricty])
I tried to search online but couldn't; find a solution 

[katika555]

Hi, 

Without Index column, just add column in dataset 

Col Electricity = 'Table'[Electricity]

[katika555]

@Oelshamy  please see attache file:

https://drive.google.com/file/d/185P7pLdhp5IROAmY4aJtLkky9QFtwtI_/view?usp=share_link 

[Kumail]

Hello @Oelshamy 

The sample solution images are attached for our quick reference. 

The pbix file is given below if you are looking to review it. 

https://drive.google.com/file/d/1CvvKwkhsxMDFRps_QPEM7KgaQjzo26vD/view?usp=share_link

Regards

Kumail Raza

https://www.linkedin.com/in/kumail-raza-76508856/

[Oelshamy · Accepted solution]

Thank you Kumail, 

The total should be 40,308 

I have found the formula that shows the correct total: 

Create a measure that has the if condition: 

Measure 1 = IF('Table',Consumption>1000), Value 1 , Value 2 ) 

Then create another measure with Sumx as below: 

Measure 2 = sumx(values('Table'[Meter No]),[Measure 1)

This worked well 

[Oelshamy]

Thank you very much for providing the required ... but it didn't work as the pbix, i am not sure why !! 

it's giving my 2,185. 

is it because consumption is a measure as well? 

[wdx223_Daniel]

when you are using a measure in the row context, it will transform the row context into filter context, that will be a disaster, try to change the first parameter of SUMX into a columns combination of which is showed in your visual, instead of the whole table.

[Vikas_Yadav]

In your formula, Column Name is Electricity and in the image you provided Column Name is Cost.

So, try this: SUMX('Table','Table'[Cost]

Always verify that the table name and column name are correct.

[Oelshamy]

Hello @Vikas_Yadav , Thank you for your reply 

I renamed the column to cost as an alias, the measure name is Electricity 

[Anonymous]

Hi @Oelshamy ,

I have created a simple sample, please refer to it to see if it helps you.

Add an index in Power Query or found a unique value replace the index.

Create a measure based on [Electricity].

Measure = var _b=SUMMARIZE('Table','Table'[Index],"aaa",[Electricity])
return
IF(HASONEVALUE('Table'[Index]),[Electricity],SUMX(_b,[aaa]))

If I have misunderstood your meaning, please provide mroe details with your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oelshamy]

@Greg_Deckler ... Thank you very much for replying. 
of course i have voted for it . it's really frustrating and understandable,  it should be so easy to get the sum right from table visual !!! 
Can you advise me what should be the correct formula here, i watched your videos, but still can't figure it out ! 
Thank you and hope your topics gets approved by MS and fix this ! 

[Greg_Deckler]

@Oelshamy First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8