Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Visualising Previous Hour Average Via Scorecard (not table)

Hi Everyone, 

 

I've been trying to visualise variance in previous hour sales vs. this hour sales through a scorecard. I've figured out how to get previous hour sales, but it seems to only work when i visualise it using a table. When i use a scorecard, i keep getting BLANKS. 

 

This is what i'm using to get previous hour sales. 

 AVG Sales Last Hour= CALCULATE(
AVERAGEX(SUMMARIZE(Sales, Sales[Time], "AverageSales", SUM(Sales[Value])), [AverageSales]),
FILTER(
ALLSELECTED(Sales),
DATEDIFF(
'Sales'[Time],
SELECTEDVALUE(Sales[Time]),
HOUR
)= 1
)
)
 
When i visualise in a table, i get the correct output (please see below), however, when i put it into a scorecard, i keep getting BLANK for the 'AVG Sales Last Hour' measure. Does anyone know how i can visualise in a scorecard and show previous hour average? 
 
TimeAVG Sales This HourAVG Sales Last Hour
9:00:2026.5 
9:15:2026.5 
9:30:2026.5 
9:45:2026.5 
10:00:2031.026.5
10:15:2031.026.5
10:30:2031.026.5
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous ,

    In case you need it as a column

     

    Column = AVERAGEX(FILTER('Table',HOUR('Table'[Time]) = HOUR(EARLIER('Table'[Time]))-1),[AVG Sales This Hour])

     

    As measure

    Measure = AVERAGEX(FILTER(ALLSELECTED('Table'),HOUR('Table'[Time]) = HOUR(max('Table'[Time]))-1),[AVG Sales This Hour])

     

    File Attached after Signature

     

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous ,

    In case you need it as a column

     

    Column = AVERAGEX(FILTER('Table',HOUR('Table'[Time]) = HOUR(EARLIER('Table'[Time]))-1),[AVG Sales This Hour])

     

    As measure

    Measure = AVERAGEX(FILTER(ALLSELECTED('Table'),HOUR('Table'[Time]) = HOUR(max('Table'[Time]))-1),[AVG Sales This Hour])

     

    File Attached after Signature

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak  Thanks Amit, do you know a way to do it with a measure? i'm not able to create a column due to the nature of the data (realtime). It doesn't get saved as it's a live connection so i can only work with measures.