How to divide values using the total of a column rather than taking an average of the values in col?

[Anonymous]

My Data

Day               Col 1            Col 2               Average

      1           1                    1                        100%

        2          1                     0                         ""

          3        ....... and on and on

Total         36                     54                     100%

Column 1 and 2 only have 1's or zeros. 

I want the average of the 36/54 rather than an average of the 100%'s. The average column is the calc col 1 / col 2. 

What can I do here? I guess an easy way to describe it is that I want the total weighted average

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

It seems you want to change the result of a visual. If you don't have any filter in column [day], you can try a formula like this:

Average =
IF (
    ISFILTERED ( Table1[Day] ),
    DIVIDE ( MIN ( 'Table1'[Col1] ), MIN ( 'Table1'[Col2] ), 1 ),
    DIVIDE ( SUM ( Table1[Col1] ), SUM ( Table1[Col2] ), 1 )
)

If you don't have any aggregated function applied, you can try a formula like this:

Average 2 =
DIVIDE ( SUM ( Table1[Col1] ), SUM ( Table1[Col2] ), 1 )

Best Regards!

Dale

[Anonymous]

Create a measure that gets the sum from col 2 at the month level. Something like:

Calculate(SUM(MyData[col2]),All(MyData[Day])

This will remove any filter on the day, summing up col2.