Reference Measures for Count and Sum Functions

[Anonymous]

Hi, I need to replicate Excel functions like Countif/Sumif, but since I'm using measures, the CALCULATE(..,FILTER()) approach hasn't been working for me.

My data starts off like this. Users (identified with PS Number) are paid every month with different types of payments (shown by Pay Type)

I have written a function to get the difference between payments of the most recent period and the last period (in this example, this week and last week). 

I now need to get: 

1. How many of each type of payment is above 10% of the entire difference: Countif(Individual Pay Type/Total of that Pay Type >= 0.1)
2. How many of each type of payment is below 10% of the entire difference: Countif(Individual Pay Type/Total of that Pay Type < 0.1)
3. How many of each type of payment is above 3000: Countif(Individual Pay Type >= 3000)

I hope to put each of these in a KPI Visual so I need to write a measure for these.

This is what I have so far but when I sum the Above 10% column it evaluates to 1 instead of 3. 

Here are my measures.

Weekly Total Gross = 
VAR Current_Week = MAX(fact_Weekly[Date])
VAR Past_Week = CALCULATE(MAX(fact_Weekly[Date]),fact_Weekly[Date]<>MAX(fact_Weekly[Date]))
VAR Current_Sum = CALCULATE(SUM(fact_Weekly[Value]),fact_Weekly[Date]=Current_Week)
VAR Past_Sum = CALCULATE(SUM(fact_Weekly[Value]),fact_weekly[Date]=Past_Week)
RETURN
Current_Sum - Past_Sum

Every Weekly = 
CALCULATE([Weekly Total Gross],ALL(fact_Weekly[PS Number]))

Weekly % of Total = DIVIDE([Weekly Total Gross],[Every Weekly],0)

Above 10% = IF([Weekly % of Total]>=0.1,1,0)

Sum of Above 10% = Calculate(COUNTROWS(_bettermeasures),filter(_bettermeasures,[Above 10%]=1))

_bettermeasures is my Measure Table and fact_Weekly is the table with my data.

Hope you can help me with this.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please try the following formula:

Sum of Above 10% = SUMX ( ALLSELECTED ( fact_Weekly[PS Number] ), [Above 10%] )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This worked perfectly for me, thank you very much @v-kkf-msft! 

[Anonymous]

Hi @amitchandak,

Thank you very much for the quick response.

I am receiving a value now for those above 10% of the total payment. However, this value doesn't hold when the table is filtered.

For instance the value should be 3 in this table (since there are 3 values above 151,000*0.1) but the measure returns a 1.

Thank you again for your help.

[amitchandak]

@Anonymous , You need some group by to filter a measure

example

Countx(filter( values(fact_Weekly[Pyement_type]),[Above 10%]=1),[Pyement_type])

Here payment_type have been used to group data to check measure Above 10% =1