Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Row level calculation

Hi,

 

i have created a measure but it is showing same value for all the months

 

formula=CALCULATE(DISTINCTCOUNT(Query1[Id]),FILTER(ALL(Query1),Query1[Status]="Qualified"||Query1[Status]="Disqualified"))

 

I have got the count if id's by above formula.

can anyone help me how can i get values for individual month

 

 

 
 
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ,

     

    Create a measure similarly as below:

     

    Measure = 
var a= CALCULATE(DISTINCTCOUNT('Table'[Id]),FILTER(ALLEXCEPT('Table','Table'[Agent name ],'Table'[Country]),'Table'[Status]="Qualified"))
var b= CALCULATE(DISTINCTCOUNT('Table'[Id]),FILTER(ALLEXCEPT('Table','Table'[Agent name ],'Table'[Country]),'Table'[Status]="Disqualified"))
Return
DIVIDE(a,b)

     

    And you will see:

    For the related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    try smth like

    formula=CALCULATE(DISTINCTCOUNT(Query1[Id]),FILTER(ALLEXCEPT(Query1, Query1[Month]),Query1[Status]="Qualified" || Query1[Status]="Disqualified"))

    but it depends on how exactly do you store a month column

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      az38 

       

      Thanks, it is working

       

      I'm doing a division which caculates the percentage

      Perc= [#Qualifiedleads]/[#Disqualifiedleads]

       
      THis is also showing 20 % for all months .
      How can i overcome this?
      • az38's avatar
        az38
        Community Champion
        6 years ago

        Anonymous 

        divide this measure by your, like

        = DIVIDE(
CALCULATE(DISTINCTCOUNT(Query1[Id]), FILTER(ALLEXCEPT(Query1, Query1[Month]), Query1[Status]="Qualified" || Query1[Status]="Disqualified")),
CALCULATE(DISTINCTCOUNT(Query1[Id]), FILTER(ALL(Query1), Query1[Status]="Qualified" || Query1[Status]="Disqualified"))
)