Counting Row Greater than / Between X

stefansingh1 · June 16, 2021, 2:19pm

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 .

Melissa · June 16, 2021, 3:35pm

Hi @stefansingh1,

Please see if this meets your requirement.

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 =
    ADDCOLUMNS(
        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.
I hope this is helpful.

stefansingh1 · June 16, 2021, 4:30pm

Melissa:

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

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

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

stefansingh1 · June 16, 2021, 5:14pm

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.

Melissa · June 16, 2021, 6:44pm

Hi @stefansingh1,

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 =
    ADDCOLUMNS(
        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 =
    ADDCOLUMNS(
        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.

.
I hope this is helpful.

EnterpriseDNA · June 18, 2021, 1:19am

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!

EnterpriseDNA · June 21, 2021, 12:51am

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.

EnterpriseDNA · June 22, 2021, 12:28am

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.

stefansingh1 · June 24, 2021, 12:30pm

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

stefansingh1 · June 24, 2021, 4:53pm

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

stefansingh1 · June 24, 2021, 8:50pm

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

stefansingh1 · June 24, 2021, 9:32pm

Melissa:

# of Days within +/- 5% Call Volume Variance v3 = 
VAR vTable =
    ADDCOLUMNS(
        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 )

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

EnterpriseDNA · June 30, 2021, 12:42am

Bumping this post for more visibility.

ankit · July 4, 2021, 2:34am

Hi @stefansingh1

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 =
    ADDCOLUMNS(
        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

EnterpriseDNA · July 5, 2021, 2:30am

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!

EnterpriseDNA · July 5, 2021, 9:30pm

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.