Calculate count of Unique ID with overall average above threshold per week

[IdrisK]

Good day all,

This forum has been very helpful to me, but I am in need of some assistance.

In the image below you will see the dataset I am working with.

The idea is for each week, I would like to get the count the number of "Eutrancell FDD" whose overall weekly Average "DL PRB Utilization" is >80

Some of the DAX formulas I have used give me the same count for each week.

Count of Cells DL PRB > 80% = CALCULATE(DISTINCTCOUNT(bm_weekly_congested_sector[Eutrancell FDD]),FILTER(bm_weekly_congested_sector,bm_weekly_congested_sector[Week of Year]=Counts[Week of Year]),FILTER(VALUES(bm_weekly_congested_sector[Eutrancell FDD]),CALCULATE(AVERAGE(bm_weekly_congested_sector[DL PRB Utilization %]))>80))

Count of Cells DL PRB > 80% = CALCULATE(DISTINCTCOUNT(bm_weekly_congested_sector[Eutrancell FDD]),FILTER(VALUES(bm_weekly_congested_sector[Eutrancell FDD]),CALCULATE(AVERAGE(bm_weekly_congested_sector[DL PRB Utilization %]),VALUES(bm_weekly_congested_sector[Week of Year]))>80))

 I am trying to achieve this format

[v-echaithra]

Hi @IdrisK ,

Thank you @ryan_mayu , @Ashish_Mathur , @Zanqueta  for your inputs.

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards.

[IdrisK]

I was able to get the desired results by combining parts from @ryan_mayu and @Zanqueta 

Weekly Avg DL PRB =

CALCULATE(

AVERAGE(bm_weekly_congested_sector[Avg DL PRB Utilization %]),

ALLEXCEPT(

bm_weekly_congested_sector,

bm_weekly_congested_sector[Eutrancell FDD],

bm_weekly_congested_sector[Week of Year]

)

)

Count of Cells DL PRB > 80% = CALCULATE(DISTINCTCOUNT(bm_weekly_congested_sector[Eutrancell FDD]),FILTER(bm_weekly_congested_sector,bm_weekly_congested_sector[Week of Year]=Counts[Week of Year]&&bm_weekly_congested_sector[Weekly Avg DL PRB]>80))

[ryan_mayu]

@IdrisK 

maybe you can try this

Weekly Avg DL PRB =
CALCULATE(
AVERAGE(bm_weekly_congested_sector[DL PRB Utilization %]),
ALLEXCEPT(
bm_weekly_congested_sector,
bm_weekly_congested_sector[Eutrancell FDD],
bm_weekly_congested_sector[Week of Year]
)
)

Count of Cells DL PRB > 80% =
CALCULATE(
DISTINCTCOUNT(bm_weekly_congested_sector[Eutrancell FDD]),
FILTER(
VALUES(bm_weekly_congested_sector[Eutrancell FDD]),
CALCULATE(
AVERAGE(bm_weekly_congested_sector[DL PRB Utilization %])
) > 80
)
)

if this does not work, pls provide the sample data (not the screenshot) and the expected output based on the sample data you provided.

[IdrisK]

Hello everyone. Thank you for the suggestions and support.

For the solution provided by @ryan_mayu , the first part calulates the Weekly Avg DL PRB per cell perfectly.

The second part however is not exracting the correct count of the cells per week where it is >80. I am trying to work on this part still, but I do appreciate all the support given 

[Ashish_Mathur]

Hi,

Try this measure (not calculated column formula)

Measure = AVERAGE(bm_weekly_congested_sector[DL PRB Utilization %])

Measure1 = countrows(filter(VALUES(bm_weekly_congested_sector[Eutrancell FDD]),[measure]>0.8))

[Zanqueta]

Hi @IdrisK ,

I'm not sure if I understood completely your point but, try this measure:

Count of Cells DL PRB > 80% =
VAR SummaryTable =
    ADDCOLUMNS(
        SUMMARIZE(
            'bm_weekly_congested_sector',
            'bm_weekly_congested_sector'[Week of Year],
            'bm_weekly_congested_sector'[Eutrancell FDD]
        ),
        "AvgDLPRB", 
            CALCULATE(AVERAGE('bm_weekly_congested_sector'[DL PRB Utilization %]))
    )
RETURN
    COUNTROWS(
        FILTER(
            SummaryTable,
            [AvgDLPRB] > 80 &&
            'bm_weekly_congested_sector'[Week of Year] = MAX('bm_weekly_congested_sector'[Week of Year])
        )
    )

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[IdrisK]

Hello @Zanqueta , thank you for responding with the DAX formula. After applying, the result seems to be giving me what I assume is the overall PRB average >80 for all the weeks.

Essentially I am trying to get this information in this format

Would it help if I shared the project and data source file?

[Zanqueta]

Hi @IdrisK, thank you for the feedback. For sure, a sample data look like at your first image could be helpfully.