Data grouping and filtration with DAX

[Cado_one]

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 !

Thanks in advance,

Cado

[littlemojopuppy]

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.

[Cado_one]

Hi @littlemojopuppy 

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,

Cado

[littlemojopuppy]

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()
	)

[Cado_one]

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 ?

[littlemojopuppy]

D'oh!!!

Try this instead...

Plants Achieving Warranty Redux = 
    VAR	PlantSummary =
    	ADDCOLUMNS(
            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()
	)

[Cado_one]

Hi @littlemojopuppy 

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 !

[littlemojopuppy]

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]
	)

[Cado_one]

Hi @littlemojopuppy ,

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,

Cado