create total of column across entire table

[AJPF]

I have two columns in my data: "start" is always 1, and "end" is either 0 or 1. 

I need to work out the percentage of "end" as a proportion of "start", but across the entire dataset, because I want to use this figure as a constant in the chart analytic. 

So my problem is threefold: 

1) create overall total of "start" and "end" columns

2) create percentage of total "end" over total "start"

3) can I use this calculated figure as a constant in my chart? 

I've tried using List.Sum in Power Query but I keep getting either spelling errors or data errors. 

If the answer to (3) is no, I may not need answers to (1) and (2)! 

[v-alq-msft · Accepted solution]

Hi, @AJPF 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create two custom columns as below with the following m codes and set data type for each of them.

=List.Sum(#"Changed Type"[Start])

=List.Sum(#"Changed Type"[End])/[Overall total of start]

In Power BI Desktop, you can also create two measures as below to show a constant in a card visual.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bohumil_Uhrin]

@AJPF you can try this:

let
    Source = #"your source table",
    percentage = List.Sum(Source[End]) / List.Sum(Source[Start])
in
    percentage

[AJPF]

I'm brand new to Power BI so I'm not sure exactly where to put this formula.  Is it Power Query, or as a new measure in Power BI? 

[Bohumil_Uhrin]

the code is for power query: go to View => Advanced Editor

and paste the code

make sure, you will specify your source table (Source = #"your source table",)

[AJPF]

If I wanted to calculate just the total for "END", could I use: 

Table.AddColumn(#"PREVIOUS STEP", "NEW VARIABLE NAME", each List.Sum([END))

? 

If yes, and "END" is numeric, then why would I get an error in every single row of "NEW VARIABLE NAME"?  It accepted the code as valid syntax but it is refusing to calculate it for me! 

Details of error: 

"Expression.Error: We cannot convert the value 1 to type List.
Details:
Value=1
Type=[Type]"