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]="D...
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!
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"))
)
    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      6 years ago

      Hi  Anonymous ,

       

      Or you can try measure as below:

       

       

      measure== DIVIDE(
CALCULATE(DISTINCTCOUNT(Query1[Id]), FILTER(Query1, Query1[Status]="Qualified")),
CALCULATE(DISTINCTCOUNT(Query1[Id]), FILTER(Query1, Query1[Status]="Disqualified"))
)

       

       

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        hi az38 / v-kelly-msft 

        One last help,

         

        i have created a heirarchy having 2 fields country and agent name. But when i'm selecting the measures it's value is same for all countries and agents .

        But I'm confused. Now i want the value first changed at country level and then at agent level.But in the formula which Az38 mentioned , we can mention only 1 column using filter except , How do i implement this heirarchy??