Forum Discussion

Jacqee's avatar
Jacqee
Helper I
1 year ago

Weekly total from Calculations by Day

I need to get to a Weekly total from Day Averages.  Totals should be 105.10 for Received and 89.88 for Passed

 

Here is my current data/visual.  I need to sum the numbers in blue for a Received total and the numbers in yellow for a Pass total to be put on separate cards.

No calculations I've tried have worked.  I always end up with an average of the average rather than the total of the average per day.

 

Background:  To get the data in the table above, I'm using these calculations.  (Note: I only need the data from 9/1/2024 to 1/1/2025.)  This are the measures used for each type of test:  FK Test used as the example.

FK Test Received =[FK Test Recvd]/ 'BIT Measures - Daily Average'[# of Days]

where

# of Days =CALCULATE(COUNT('Custom Calendar'[Day]),

FILTER(

        'Custom Calendar',

        'Custom Calendar'[Date] >= DATE(2024, 9, 1) &&

        'Custom Calendar'[Date] <  DATE(2025, 1, 1)),

        ALLEXCEPT('Custom Calendar', 'Custom Calendar'[Day Number]))

 

and 

 

FK Test Recvd =CALCULATE(COUNT(DataView[Day FK Test Recvd]),

USERELATIONSHIP('Custom Calendar'[Date], DataView[SenttoFkTestingDate])

)

 

The blue and yellow columns are a total of each of measures by day of the week

 

Daily Avg Overall Recvd = [Benefit Test Received] + [CP Test Received] + [FK Test Received] + [SA Test Received]

 

Thanks in advance!!

4 Replies

  • Hi,

    I am not sure if I understood your question correctly, but please try something like below whether it suits your requirement.

     

    expected result measure: =
    SUMX ( VALUES ( 'Custom Calendar'[Day] ), [Daily Avg Overall Recvd] )
    
    • Jacqee's avatar
      Jacqee
      Helper I

      That is super close but I get 112.81 for Received and 97.79 for Passed instead of 105.10 and 89.88.  Any idea why the variance?

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User

        Hi,

        if it is OK with you, please share the link of your sample pbix file, and then I can try to look into it.

        Thank you.