cancel
Showing results for
Did you mean:
Helper V

## Data grouping and filtration with DAX

Hi,

I have a tricky situation that cause me trouble with a measure, I'll try to explain it my best through an example. My english is quiet limited so don't hesitate ask me if it's not understandable.

The database file contains monthly values for several locations (like below)

 LOCATION DATE Availability realized Availability warranty Plant A january 99% 97% Plant A february 96% 97% Plant A march 98.5% 97% Plant B january 96% 95% Plant B february 95% 95% Plant B march 89% 95% Plant C january 94% Plant C february 97% Plant C march 100%

In the Power BI report one of the analysis I need to produce displays in a table visual the quarterly values as follows :

 LOCATION DATE Availability realized (avg) Availability warranty Plant A Quarter 1 97.83% 97% Plant B Quarter 1 93.33% 95% Plant C Quarter 1 97%

What I'm trying to achieve is to create a measure which calculate the percentage of plants that have its quarter average availability realized superior or equal to the availability warranty, not considering the plants that have no availability warranty.

As you can see in the example, the plant A is okay but the plant B is not and the plant C is ignored, so the measure should return 50% ( [count of locations with availability realized > availability warranty] / [count of locations with availability realized > availability warranty + count of locations with availability realized < availability warranty] )

I tried many formulas to achive my goal but none of them has worked yet and I begin to be bankrupt of ideas.

IF anyone has an idea or a working formula I'd we very glad to discuss and try it !

8 REPLIES 8
Super User I

Happy holidays!

Given that your data has two fields that can be used as dimensions (time and plant) that you mean you're trying to do something like this

It's going to do that because the measure is about summarizing plants.  Here's what the measure is doing...

1. First it creates a table summarizing performance by plant and year/quarter
2. To get the denominator it gets those plants that should be included in the calculation (you stated as a requirement if no warranty available, exclude) and then counts the rows.  If no plants have warranty available, then an empty set is returned and the resulting count is blank.
3. To get the numerator, it gets those plants that should be included AND those where realized >= warranty and then counts the rows.  If no plants have both warranty available and actual exceeds warranty, an empty set and blank count.

Plant C has no warranty available so it's excluded.  Plant B has warranty, but actual did not exceed warranty so numerator is blank, denominator is one (two plants, but filtered for Plant B only due to filter context) which results in blank.  Plant A has warranty and actual did exceed warranty so numerator is one, denominator is also one (same reasoning) so it's 100%.  Because the measure is about summarizing groups of plants you really can't use it on individual plants.

If you have other data, please share and we can look at.

Helper V

I wish you a happy new year 2021 !

I think I've understand everything you said in the previous message, and I still get trouble with that but I feel we're close. Please find on the below screenshot an extract of the data I use :

As you can see, I've putted the VAR PlantsAchievingWarranty in the numerator column and the VAR PlantsIncluded in the denominator column. And the measure that returns the ratio of Plants achieving warranty (in the extreme right column) only calculate when the numerator is not blank. Moreover, the total line seems to include the plants that have no availibility warranty.

How did you managed this ?

Thanks again,

Super User I

Good morning!  I apologize for not reading it thoroughly 🙄

Include this measure for the overall percentage

``````Plants Achieving Warranty =
VAR	PlantSummary =
SUMMARIZECOLUMNS(
Availability[LOCATION],
'Calendar'[Year],
'Calendar'[Quarter],
"AvailabilityRealized",
[Average Availability Realized],
"AvailabilityWarranty",
[Average Availability Warranty]
)
VAR PlantsIncluded =
COUNTROWS(
FILTER(
PlantSummary,
ISBLANK([AvailabilityWarranty]) = FALSE()
)
)
VAR	PlantsAchievingWarranty =
COUNTROWS(
FILTER(
PlantSummary,
[AvailabilityRealized] >= [AvailabilityWarranty] &&
ISBLANK([AvailabilityWarranty]) = FALSE()
)
)
RETURN

DIVIDE(
PlantsAchievingWarranty,
PlantsIncluded,
BLANK()
)``````

Helper V

Good evening @littlemojopuppy ,

don't worry for the error, I must say it's quiet a long and boring text to read 😅

The measure you propose seems great to me but unfortunately I got the following error when applying it to the report and puttnig it into a card visual :

I've created the date table (Calendar) with year and quarter columns as you recommended and I included your measure in the report. Do you have an idea of what could be wrong ?

Super User I

D'oh!!!

``````Plants Achieving Warranty Redux =
VAR	PlantSummary =
CROSSJOIN(
VALUES(Availability[LOCATION]),
GROUPBY(
'Calendar',
'Calendar'[Year],
'Calendar'[Quarter]
)
),
"AvailabilityRealized",
[Average Availability Realized],
"AvailabilityWarranty",
[Average Availability Warranty]
)
VAR PlantsIncluded =
COUNTROWS(
FILTER(
PlantSummary,
ISBLANK([AvailabilityWarranty]) = FALSE()
)
)
VAR	PlantsAchievingWarranty =
COUNTROWS(
FILTER(
PlantSummary,
[AvailabilityRealized] >= [AvailabilityWarranty] &&
ISBLANK([AvailabilityWarranty]) = FALSE()
)
)
RETURN

DIVIDE(
PlantsAchievingWarranty,
PlantsIncluded,
BLANK()
)``````

Helper V

Sorry for bothering you on christmas day 😅

Your measure is now half working, it returns blank when there is no availability warranty on the period selected. But when there are plants with warranty the measure returns everytime 100% even though some plants have there quarterly averaged availability realized inferior to the warranty.

I'll be in holidays from this evening to the 4th of january so I wish you happy end of the year celebrations and will come back to this topic in about ten days !

Super User I

First, change your date of "January", "February", March", etc. into actual dates.  Add a date table, mark it accordingly and make sure there is a field for quarter.

Then it becomes very easy...

``````Average Availability Realized:=AVERAGEX(
Availability,
Availability[Availability realized]
)

Average Availability Warranty:=AVERAGEX(
Availability,
Availability[Availability warranty]
)``````

Helper V

Thanks for your answer, my problem is not about creating the table for quarterly values (I already have this table visual and it works fine).

My issue is to create a measure to returns the ratio of plants that have its quarter average availability realized superior or equal to the availability warranty, as explained in the first message.

Best regards,

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks