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|
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 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,
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...
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.
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 ?
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() )
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 ?
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() )
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 !
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] )
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.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.