Sum Of Count

[Anonymous]

the above is a sample data i created, what i need to do is sum of count of 1 in measure 1 column  then divide it by total count of measure 2 (using a directly query plus unable to use seleted value function as inside selected value need to put a column name as different column being used to filter the report with personalization in power bi services unable to use one column )

[Anonymous · Accepted solution]

thanks for the help but the above did not work insteaded have used countx that worked 

[Arul]

@Anonymous ,

Your ask is little bit confusing from  this point (using a directly query plus unable to use seleted value function as inside selected value need to put a column name as different column being used to filter the report with personalization in power bi services unable to use one column ).

Could you brief it more?

Thanks,

Arul

[Anonymous]

basically just want to get the total sum of count(of 1 ) in measure 1  and then total sum of count (of 1 under measure in the above picture ) of measure 2 and then divide both of them 

[Arul]

@Anonymous ,

check if it helps or not.

Total_divide = 
VAR _countm1 = CALCULATE(SUM(Sample_T2[Measure 1]),Sample_T2[Measure 1] = 1)
VAR _countm2 = CALCULATE(SUM(Sample_T2[Measure 2]),Sample_T2[Measure 2] = 1)
return DIVIDE(_countm1,_countm2)

Total_divide = 
VAR _countm1 = CALCULATE(COUNT(Sample_T2[Measure 1]),Sample_T2[Measure 1] = 1)
VAR _countm2 = CALCULATE(COUNT(Sample_T2[Measure 2]),Sample_T2[Measure 2] = 1)
VAR _summ1 = SUMX(Sample_T2,_countm1)
VAR _summ2 = SUMX(Sample_T2,_countm2)
return DIVIDE(_summ1,_summ2)

Thanks,

Arul

[Anonymous]

@Arul 
no it is not working as when i am using count funtion it is asking me to use only a column name inside it like count(column name) as measure 1 (column) is bacially a measure so unable to use it inside count function

[Arul]

@Anonymous ,

Okay, if that is the case you can count 1and sum from source column itself instead of taking measure as a reference.

Thanks,

Arul