cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bolabuga Helper V
Helper V

query editor calculation.

Capturar.PNG

 

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
CahabaData Solution Sage
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.

www.CahabaData.com
bolabuga Helper V
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.

bolabuga Helper V
Helper V

Re: query editor calculation.

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

 

http://community.powerbi.com/t5/Desktop/Creating-a-newer-table-based-on-data-of-existing-calculated/...

 

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

BhaveshPatel Community Champion
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.

B1.PNG

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
bolabuga Helper V
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.

CahabaData Solution Sage
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)

 

 

www.CahabaData.com
bolabuga Helper V
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.

CahabaData Solution Sage
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. 

www.CahabaData.com
Microsoft v-haibl-msft
Microsoft

Re: query editor calculation.

@bolabuga

 

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

 

Best Regards,

Herbert

 

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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