Count the frequency of a measure

[EdAbada]

Hello, i am relatively new to POWER BI and DAX. Usually i am able to learn by watching youtube trainings or google, but i have something i want to do that i could not solve.

Ok, here is my data set.....

And i was able to make a neat summary by making measures (TotalSales, TotalTarget, and Achievement)

TotalSales =  SUM(Sheet1[Sales])

TotalTarget = SUM(Sheet1[Target])

Achievement = [TotalSales] / [TotalTarget]

What i want to do next that i could not figure out is to summarize the measure ACHIEVEMENT into something like this which basically shows the number of people who achieved 100% or higher per Area.

It seems like a simple thing but im stuck. Any help would be appreciated. Thanks!

[BeemsC]

Hello,

For this i recommend using the CALCULATE() function.
Ur formula would look something like:
Formula = CALCULATE(SUM('sheet1'[Sales]);'sheet1'[target] => 100)

Try recreating this in your own pbi file, since this was from memory.

Good luck

Edit: See below

[EdAbada]

Hi BeemsC, thanks for replying.

I'm still trying to make sense of your formula, however, i think the formula should count the frequency of the measure Achievements with results  of 100% and up and not using the measure Target which is in the formula you provided.  Appreciate the reply though...

[BeemsC]

As i said, since i dont have data, the formula might not be 100% correct.
If you don't understand give me some sample data, and ill make it for you

[EdAbada]

I can send you the pbix file that i have but i dont know how to attach a file here. Maybe you can pm me an email address i can send to?

The small data im using is the one in my first post (first pic). The 2nd pic is the matrix i made using a few measures that i created. The 3rd pic is the table or matrix that i hope to make to show how many people in each area having 100% and higher achievement.

Thanks for your assistance on this.

[BeemsC]

It seems i got the wrong idea earlier, my bad.
How about the following:

Achievers > 100 = CALCULATE(COUNT(Sheet1[Achievement]);Sheet1[Achievement] > 1)

[Zubair_Muhammad · Accepted solution]

Hi @EdAbada

Try this Measure

Achievers > 100% =
COUNTROWS (
    FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Area] ), CALCULATE ( [Achievement] ) > 1 )
)

 

[EdAbada]

Thats it! Thank you Zubair_Muhammad for your measure.  It works!

BeemsC, i also tried your measure but it gives me an error not sure why. Nevertheless, thank you for your help.

[BeemsC]

Ill try to create something myself for now