# Counting Row Greater than / Between X

Variance Forecast.zip (783.3 KB)

Hello

I need help with creating a measure that returns the count of results from an existing measure.

In the PBIX attached, I am trying to find the # of Days that fall within +/- 5% for this existing measure :Call Volume Variance All = DIVIDE([Call Volume Difference],sum(Data_Entry[Calls Offered Forecast]))

My fact table is at the daily level and has duplicate days. The measure needs to be dynamic to adjust to filters based on Center and Call Type.

Once the measure is created, I need to answer the question : What % of days are have a less than or equal to 5 % call volume variance. I would also need to

Any help would be appreciated. I tried watching videos and reading forums but I cannot seem to get it right .

Because we are leveraging the fact table, you might want to limit the number of columns passed to the virtual table. Using SELECTCOLUMNS, read up on it here.

``````# of Days within +/- 5% Call Volume Variance =
VAR vTable =
Data_Entry,
"@Value", [Call Volume Variance All]
)
RETURN

COUNTROWS(
FILTER( vTable,
[@Value] >= -0.05 &&
[@Value] <=  0.05 &&
[@Value] <>0
)
)
``````

If you are expecting a different result, please supply a mock up with expected results.

3 Likes

Thank you I will review in my model and let you know .

Call Variance_EDNA_Mockup_CountRows.xlsx (384.4 KB)

Thank you , but this is not the result I expected. I have attached an excel file with the same data.
The excel image and file attached shows that Center , LIG has 14 days where the Call Volume Variance was +/-5% . However, the measure you created shows 17. Furthermore, when I expand to days (not weeks) , it shows dates that are outside of +/- 5%. Snip from PBIX file attached.

I really appreciate all the help and the detailed explanation.

Thank you!

Something to think about. This measure summarizes the fact table BUT because we are also taking Center and Call Type into account it may still count a single date more than once for each of those combinations it encounters.

``````# of Days within +/- 5% Call Volume Variance v2 =
VAR vTable =
SUMMARIZE( Data_Entry, Dates[Date], Data_Entry[Center], Data_Entry[Call Type] ),
"@Value", [Call Volume Variance All]
)
VAR nTable =
FILTER( vTable,
[@Value] >= -0.05 &&
[@Value] <=  0.05 &&
[@Value] <>0
)
RETURN
COUNTROWS( nTable )
``````

So if we only want to count unique dates, we could do something like this.

``````# of Days within +/- 5% Call Volume Variance v3 =
VAR vTable =
SUMMARIZE( Data_Entry, Dates[Date], Data_Entry[Center], Data_Entry[Call Type] ),
"@Value", [Call Volume Variance All]
)
VAR nTable =
DISTINCT(
SELECTCOLUMNS(
FILTER( vTable,
[@Value] >= -0.05 &&
[@Value] <=  0.05 &&
[@Value] <>0
),  "@Date", [Date]
)
)
RETURN
COUNTROWS( nTable )
``````

.
And hereâ€™s the difference between the two. On an individual level it will still show 1 for each occurrence BUT on an aggregated level it will only count distinct dates.

.

2 Likes

Hi @stefansingh1, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope youâ€™ll give your insights on how we can further improve the Support forum. Thanks!

Hi @stefansingh1, weâ€™ve noticed that no response has been received from you since the 17th of June. We just want to check if you still need further help with this post? In case there wonâ€™t be any activity on it in the next few days, weâ€™ll be tagging this post as Solved.

Hi @stefansingh1, due to inactivity, a response on this post has been tagged as â€śSolutionâ€ť. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Thanks - Will give it a try today and let you know the outcome.

Thank you - v3 is exactly what I was looking for.

2 Likes

@Melissa - Thank you once again - This solution worked like a charm. How can I count the total number of days that have a variance so that I can then compute the percentage of days that are +/- 5 % . ?

1 Like

Hello ,

The v3 formula is working for the most part but it seems to be ignoring the dates where Call Volume Variance All = 0.00 % . Image attached. Filters applied : Date May 1 - May 31st , Center = LIG , Call Type = General

Bumping this post for more visibility.

There is a condition â€ś&& [@Value] <>0â€ť. If you comment it out or remove it as done below you shall get the desired result.

``````# of Days within +/- 5% Call Volume Variance v3 =
VAR vTable =
SUMMARIZE( Data_Entry, Dates[Date], Data_Entry[Center], Data_Entry[Call Type] ),
"@Value", [Call Volume Variance All]
)
VAR nTable =
DISTINCT(
SELECTCOLUMNS(
FILTER( vTable,
[@Value] >= -0.05 &&
[@Value] <=  0.05
//       &&  [@Value] <>0
),  "@Date", [Date]
)
)
RETURN
COUNTROWS( nTable )
``````

Thanks
Ankit J

2 Likes

Hi @stefansingh1, did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope youâ€™ll give your insights on how we can further improve the Support forum. Thanks!

Hi @stefansingh1, due to inactivity, a response on this post has been tagged as â€śSolutionâ€ť. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.