DAX Grouping Totals

[spandy34]

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 

[danextian · Accepted 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).

[spandy34]

Thats great - thank you so much for your help.  Its worked .