Powerpivot DAX formula to build histogram?

Hi,

I'm just rying to build a DAX formula to allow a Histogram to be built but failing horribly and hoping someone might be able to point me right?

Basically I have a whole bunch of rows of data taken every 5 minutes and I would like to be able to report how much time is spent in each zone, for example how long the values remain between 50 and 60.

In english this would write something like this where VALUE is the column of 5 minute data, instances of cells which match the critera mutiplied by 12 to give hours:

Hours Spent At = COUNT(VALUE,VALUE>50 and VALUE<60) * 12

My best attemp in DAX is this:

V5060:=COUNTX(RawData,[VALUE]>=50&<60)*12

This is very wrong but not sure how to correct it?

Thanks in advance.

Can you upload sample of your data? Enough rows to demonstrate your need.

Hi CK76,

I think I worked it out by using the calculate function:

Time Spent:=(CALCULATE(COUNT([VALUES]),RawData[VALUES]>=50,RawData[VALUES]<60)/12)+0

The +0 at the end is to force a '0' to return instead of 'BLANK'

CALCULATE is very useful to learn.

You can find detailed breakdown in link below.
http://sqlblog.com/blogs/marco_russo...ks-in-dax.aspx

Fantastic, thanks for the link.