Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I wondered if someone could help me please.
I have a dataset which captures the actual quantity of care hours [ActualQuantity] against the planned amount of hours [PlannedActualQuantity] .
Below I have a measure that counts the number of records where the ActualQuantity is 25% more that the PlannedActualQuantity.
z_25% More =
COUNTROWS (
FILTER ( 'Spot Contracts', 'Spot Contracts'[ActualQuantity] > 'Spot Contracts'[PlannedActualQuantity] * 1.25 )
Each record has an associated Period Number [PeriodNo] which includes 28 days. Each period includes 28 days. Instead counting the total ActualQuantity against PlannedActualQuantity for each visit (or record) that are 25% more, I have been asked to count the total ActualQuantity against PlannedActualQuantity for each Period that are 25% more, so how many periods have a total of 25% more ActualQuantity than PlannedActualQuantity.
Can anyone please help me amend this measure?
@EylesIT @amitchandak @tamerj1 @danextian @goncalogeraldes @Greg_Deckler
Solved! Go to Solution.
Hi @spandy34 ,
Based on the information provided, try this measure:
Count =
VAR __SUMMARY =
ADDCOLUMNS (
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[Period] ),
"@Actual", CALCULATE ( SUM ( 'Table'[Actual] ) ),
"@Planned", CALCULATE ( SUM ( 'Table'[Planned] ) )
),
"@Rate", DIVIDE ( [@Planned], [@Actual] )
)
RETURN
COUNTROWS ( FILTER ( __SUMMARY, [@Rate] >= 1.25 ) )
Just change the table and column names accordingly.
If this isn't the case, please provide a sample data and your expected result (please do this for your future posts, not everyone has the time to create a sample data).
Proud to be a Super User!
Hi @spandy34 ,
Based on the information provided, try this measure:
Count =
VAR __SUMMARY =
ADDCOLUMNS (
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[Period] ),
"@Actual", CALCULATE ( SUM ( 'Table'[Actual] ) ),
"@Planned", CALCULATE ( SUM ( 'Table'[Planned] ) )
),
"@Rate", DIVIDE ( [@Planned], [@Actual] )
)
RETURN
COUNTROWS ( FILTER ( __SUMMARY, [@Rate] >= 1.25 ) )
Just change the table and column names accordingly.
If this isn't the case, please provide a sample data and your expected result (please do this for your future posts, not everyone has the time to create a sample data).
Proud to be a Super User!
Thats great - thank you so much for your help. Its worked .
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |