topic Re: Count of days with measurement under certain percentage in DAX Commands and Tips

Count of days with measurement under certain percentage

drew61199 — Tue, 07 Jun 2022 15:25:05 GMT

I'll preface by saying I'm new to DAX / PBI. I have two fields - "possible" and "actual" - for multiple machines and also daily data for each. I created a new measure to create a utilization percentage. I need to count the instances where this is less than 65%, but cannot figure it out. As an example of what I'm trying to achieve:

Machine A - Mon 80%, Tues 80%, Wed 80%
Machine B - M 50%, T 50%, W 60%
Machine C - M 70%, T 45%, W 85%

I would expect to get 4 in this example. My dataset is signifcantly larger, but I'm way low on my figures. I assume b/c I'm only counting days and not days AND machines?! If so....how do I alter my formula correctly?

_Utilization=DIVIDE(SUM('PowerBI_DATA'[Actual]), SUM('PowerBI_DATA'[Possible]))

_Under_Utilized=CALCULATE(COUNT(PowerBI_DATA[Date]),FILTER('Facility Data',[_Utilization] < .65))

Re: Count of days with measurement under certain percentage

tamerj1 — Tue, 07 Jun 2022 15:36:28 GMT

drew61199

Would you please share a screenshot of your visual hiding any sensitive data? I guess the count will be displayed in a card visual?

Re: Count of days with measurement under certain percentage

drew61199 — Tue, 07 Jun 2022 15:40:39 GMT

I'm just building it out, but yes, as a test, I'm using it in a card. I will later utilize in barcharts, etc to showcase top opportunities (based on lowest utilization). I'm comparing my figures to the QLIK visual I have that PBI is replacing, but I've drilled to individual items and a small subset of the data (single week) and verified the formula above is not accurate

Re: Count of days with measurement under certain percentage

tamerj1 — Tue, 07 Jun 2022 16:28:22 GMT

drew61199

Thank you for clarification. What are you slicing by when using the [_Utilization] measure?

Re: Count of days with measurement under certain percentage

tamerj1 — Tue, 07 Jun 2022 16:48:41 GMT

drew61199

Sorry I think I was not clear in my question.
The utilization value that you are trying to count is based on machine/date? Are we talking about daily basis? And do you have a date table? If not please clarify. Thank you

Re: Count of days with measurement under certain percentage

tamerj1 — Tue, 07 Jun 2022 17:02:36 GMT

drew61199

However, please try

_Under_Utilized = SUMX ( CROSSJOIN ( VALUES ( 'Date'[Date] ), VALUES ( TableName[Machine ID] ) ), CALCULATE ( IF ( [_Utilization] < 0.65, 1 ) ) )

Re: Count of days with measurement under certain percentage

drew61199 — Tue, 07 Jun 2022 17:07:46 GMT

The date comes in with the data feed. Multiple columns - physical location hierarchy (Area, District, Facility), Date, Machine ID, and aforementioned actual / possible throughput. I will be slicing by the hierarchy to show top opportunity Area, District, Facility, Machine ID

Re: Count of days with measurement under certain percentage

tamerj1 — Tue, 07 Jun 2022 17:26:14 GMT

drew61199

Ok please try

_Under_Utilized = SUMX ( SUMMARIZE ( TableName, TableName[Date], TableName[Machine ID], TableName[Area], TableName[District], TableName[Facility] ), CALCULATE ( IF ( [_Utilization] < 0.65, 1 ) ) )

Re: Count of days with measurement under certain percentage

drew61199 — Mon, 13 Jun 2022 16:15:22 GMT

Sorry for delayed response....had some vacation days.

It seemed to result in too high of a number when I had all of the hierarchy levels summarized, but worked great with simply the date and machine ID.

Thank you so much!