Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Row level calculation

Hi,

 

row.jpg

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

 

 

 
 
1 ACCEPTED SOLUTION

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:

Annotation 2020-04-27 160351.pngAnnotation 2020-04-27 160432.png

For the related .pbix file,pls click here.

 

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

View solution in original post

6 REPLIES 6
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

@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?

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
Not applicable

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??

 

 

 

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:

Annotation 2020-04-27 160351.pngAnnotation 2020-04-27 160432.png

For the related .pbix file,pls click here.

 

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

@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"))
)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.