cancel
Showing results for
Did you mean:
Helper V

## query editor calculation.

Hello, i would like some help, im trying to do the following on the query editor.

as shown on the screenshot for the columns, i need to do:

[sum of consumes] divided [sum of homes] filtered when [type = "R"] and [ identification = "condominios" ]

thks.

9 REPLIES 9
Solution Sage

## Re: query editor calculation.

You seek a single value - that is displaying in a new column - and that single value is the same on every row?

If not please clarify with an example of a few rows that are your goal.

Helper V

## Re: query editor calculation.

yes cahaba, thats exactly what i seek **1 value on every row**, in truth i need to do several calculations with single results, because i need the average results of a year, for some indicators.

The example above would cover me for every calculation that i need to do, because everything uses the same pattern in the calculations, just the filters would be different.

Helper V

## Re: query editor calculation.

Xiauxian posted a solution on my other thread, using DAX commands.

However, if i can learn to do the some calculations on the query editor, more knowledge are always good :).

Community Champion

## Re: query editor calculation.

You can use something like this and once you get your desired column, you can do some either using Invoke Custom Function or Custom Column.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Helper V

## Re: query editor calculation.

Thks Bhavesh, but i dont think i can use that example.

Because the columns that i need to divide have lots of differents values, or at least im not seeing how this would apply to my case where i want 1 value across all rows in a new column, and this value is the result of the formula above.

Solution Sage

## Re: query editor calculation.

I would suggest then that you keep things simple and do it step by step rather than in one task.

Make a calculated column:    sumC = Sumx(Table,Field)

Make a calculate column:      sumH = Sumx(Table,Field)

this allows you to visually verify

then make a calculated column: Final = IF([type] = "R" AND [identification] = "condominios"),sumC/sumH,0)

Helper V

## Re: query editor calculation.

Interesting approach, i will test to see how things look like with this. I actually did all in one go with "calculate", "divide" and "filter", but the way you suggested is simpler.

Any way, thks both cahaba and bavesh, to learn how to use chains of DAX commands in powerquery i will probably need to do a online course or something that is more time consuming. I just read "Power Query Formula Language Specification (August 2015)", but its all CLOUDY right now hauahuah.

Solution Sage

## Re: query editor calculation.

actually my suggestion renders a different value per row - - and am not sure that is what you seek based upon our original exchange.

Microsoft

## Re: query editor calculation.

@bolabuga

Have you got this problem solved with CahabaData’s solution?

Best Regards,

Herbert

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors