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