query editor calculation.

[bolabuga]

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.

[BhaveshPatel]

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.

[bolabuga]

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]

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)

[bolabuga]

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.

[v-haibl-msft]

@bolabuga

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

Best Regards,

Herbert

[CahabaData]

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

[CahabaData]

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.

[bolabuga]

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 :).

[bolabuga]

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.