topic Re: Trying to get total sum of measure correctly in DAX Commands and Tips

Trying to get total sum of measure correctly

Oelshamy — Mon, 07 Nov 2022 12:59:21 GMT

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

Re: Trying to get total sum of measure correctly

Greg_Deckler — Mon, 07 Nov 2022 13:21:39 GMT

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

Re: Trying to get total sum of measure correctly

Oelshamy — Tue, 08 Nov 2022 04:57:36 GMT

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 !

Re: Trying to get total sum of measure correctly

Anonymous — Tue, 08 Nov 2022 06:23:50 GMT

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.

Re: Trying to get total sum of measure correctly

Vikas_Yadav — Tue, 08 Nov 2022 06:59:07 GMT

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.

Re: Trying to get total sum of measure correctly

Oelshamy — Tue, 08 Nov 2022 07:27:36 GMT

Hello Vikas_Yadav , Thank you for your reply

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

Re: Trying to get total sum of measure correctly

wdx223_Daniel — Tue, 08 Nov 2022 07:58:00 GMT

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.

Re: Trying to get total sum of measure correctly

Kumail — Tue, 08 Nov 2022 08:47:03 GMT

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/

Re: Trying to get total sum of measure correctly

katika555 — Tue, 08 Nov 2022 09:10:57 GMT

Hi,

Without Index column, just add column in dataset

Col Electricity = 'Table'[Electricity]

Re: Trying to get total sum of measure correctly

Oelshamy — Tue, 08 Nov 2022 12:17:18 GMT

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?

Re: Trying to get total sum of measure correctly

katika555 — Tue, 08 Nov 2022 15:08:37 GMT

Oelshamy please see attache file:

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

Re: Trying to get total sum of measure correctly

Oelshamy — Mon, 14 Nov 2022 05:13:10 GMT

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