How to calculate how many ppl reach the targets?

[ryan_mayu]

Hi all

I have a data format like below. Is it possible to use DAX to calculate how many ppl reach the targets?

The result should be 1 becuase only a meet the target for Q1. B doesnot met the target for Q1. C doesnot have the target, so c should be excluded.

What's more , is it possbile to calcuate how many people do not meet the target? Those people do not have targets should be excluded. The result should be 1 as well.

Thanks in advance.

Ryan

[v-piga-msft]

Hi @ryan_mayu ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your desired output so that we could understand your logic better.

Best Regards,

Cherry

[Anonymous · Accepted solution]

Hi Ryan

Ideally you would want your Revenue and Target data in seperate tables.

Next you need a value common to both sets of data. You mention in your explanation Q1, so i guess you want to evaluate these over Quarters? So perhaps a column showin Q1, Q2 etc?

Then you simply need a DAX CALCULATE(SUM()) formula to get the sum of revenue for each name per Qtr and compare that to the Target for the same Qtr.

If you stay with a single table, you would stil ideally want the extra common colum, but then your CALCULATE(SUM())  would need to include "revenue" or "target" as criteria.

Quick and dirty is;

Total rev Qtr = CALCULATE(SUM(Table1[amount]),filter(Table1,Table1[datatype]="revenue" && Table1[name] = EARLIER(Table1[name]) && Table1[Qtr] = EARLIER(Table1[Qtr])))

total target Qtr = CALCULATE(SUM(Table1[amount]),filter(Table1,Table1[datatype]="target" && Table1[name] = EARLIER(Table1[name]) && Table1[Qtr] = EARLIER(Table1[Qtr])))+0

total target Qtr = CALCULATE(SUM(Table1[amount]),filter(Table1,Table1[datatype]="target" && Table1[name] = EARLIER(Table1[name]) && Table1[Qtr] = EARLIER(Table1[Qtr])))+0

As you can see, the achieved target Qtr column shows True where achieved.