Forum Discussion

reemadsouza's avatar
reemadsouza
Frequent Visitor
2 years ago
Solved

Averages not showing properly

I have this measure :-

 

row context avg =
AVERAGEX(
    FILTER(
        'Ticket Times',
        NOT(ISBLANK('Ticket Times'[Trip Time (s)]))
    ),
    'Ticket Times'[Trip Time (s)] / 60
)
 and another way to write the same 
Column context avg = CALCULATE(DIVIDE([Trip Time (min)],[Ticket Count without Blanks]))
 
where 
Ticket Count without Blanks =
CALCULATE(
    DISTINCTCOUNT('Ticket Lines'[Ticket Number]),
    FILTER('Ticket Times', NOT(ISBLANK('Ticket Times'[Trip Time (s)])))
)
 
for 3 days it gives me these values 
 

 

 

 when I average in excel I get 

 

  how do i get my pbi measuire to match 145.75388 avg instead of 145.788

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi reemadsouza ,

    I create a table as you mentioned.

    Then I create a new table and here is the DAX code.

    Table 2 = 
SUMMARIZE(
    'Table', 
    'Table'[ID], 
    "TotalCount", SUM('Table'[Count])
)

    So you can calculate what you want.

    Average = AVERAGE('Table 2'[TotalCount])

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • ahadkarimi's avatar
    ahadkarimi
    Icon for Solution Specialist rankSolution Specialist
    2 years ago

    Hi reemadsouza, give this measure a try, and if you encounter any issues, let me know.

     

    Corrected Avg = 
VAR TotalTimeInMinutes = SUMX(
    FILTER(
        'Ticket Times',
        NOT(ISBLANK('Ticket Times'[Trip Time (s)]))
    ),
    'Ticket Times'[Trip Time (s)] / 60
)

VAR NonBlankTicketCount = 
    CALCULATE(
        DISTINCTCOUNT('Ticket Lines'[Ticket Number]),
        FILTER('Ticket Times', NOT(ISBLANK('Ticket Times'[Trip Time (s)])))
    )

RETURN
DIVIDE(TotalTimeInMinutes, NonBlankTicketCount)

    Did I answer your question? If so, please mark my post as the solution! ✔️
    Your Kudos are much appreciated! Proud to be a Solution Supplier!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi reemadsouza ,

    I create a table as you mentioned.

    Then I create a new table and here is the DAX code.

    Table 2 = 
SUMMARIZE(
    'Table', 
    'Table'[ID], 
    "TotalCount", SUM('Table'[Count])
)

    So you can calculate what you want.

    Average = AVERAGE('Table 2'[TotalCount])

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • vivek31's avatar
    vivek31
    Icon for Resolver II rankResolver II
    2 years ago

    Hii, reemadsouza

     

    you can try this,

     

    first sum of count

    total count = SUM('Table (3)'[count])

     

    second avg the total count,

    avg = AVERAGEX(VALUES('Table (3)'[id]),[total count])