cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cado_one
Helper V
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)

LOCATIONDATEAvailability realizedAvailability warranty
Plant Ajanuary99%

97%

Plant A

february

96%97%
Plant Amarch98.5%97%
Plant Bjanuary96%95%
Plant B

february

95%95%
Plant Bmarch89%95%
Plant Cjanuary94% 
Plant C

february

97% 
Plant Cmarch100% 

 

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

LOCATIONDATEAvailability realized (avg)Availability warranty

Plant A

Quarter 197.83%97%
Plant BQuarter 193.33%95%
Plant CQuarter 197% 

 

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

8 REPLIES 8
littlemojopuppy
Super User I
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

littlemojopuppy_0-1608818661618.png

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.

 

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 :

Capture d’écran 2021-01-07 160458.png

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
Super User I
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()
	)

littlemojopuppy_0-1608731264852.png

 

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 :

Capture d’écran 2020-12-23 161918.png

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 ?

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

 

littlemojopuppy_0-1608738390429.png

 

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
Super User I
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]
	)

littlemojopuppy_0-1608663151613.png

 

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors