Is it possible to summarize with a calculate and if then statement in DAX?

[Anonymous]

Hello,

I'm facing a challenge as I'm trying to learn DAX. I have this measure that I created below:

Measure 2 =

VAR availablehours =

SUM('fact AvailableTime'[DiaryTime])-SUM('fact BookedTime'[BookingHours])

RETURN

CALCULATE(IF(availablehours > 50, availablehours))

I'm stuck because in the table I want the sum to be returned based, but instead it's giving me the overall total. The 981.10 in other words is if the > 50 filter did not exist. In this case though since I am looking at > 50 I want to see the sum in the table for only that.

[v-lionel-msft]

Hi @Anonymous ,

In other words, when you filter out values >50 in the slicer, the total value of the table is wrong?

Please provide a sample .pbix file.

Best regards,
Lionel Chen

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

[mahoneypat]

To get the right total, you need to iterate over the column(s) used in your visual with either VALUES or SUMMARIZE.

CorrectSumMeasure = SUMX(VALUES(Table[Column]), [Measure 2])

CorrectSumMeasure = SUMX(SUMMARIZE(Table, Table[Column1], Table[Column2]), [Measure 2])

Pat

[Anonymous]

I ended up puzzling through and ended up with this DAX formula:

VAR availablehours =

     SUMMARIZE('dim Resource','dim Resource'[ResourceID],"Available Hours",

     CALCULATE(SUM('fact AvailableTime'[DiaryTime])-SUM('fact BookedTime'[BookingHours]),

     FILTER('fact AvailableTime',(SUM('fact AvailableTime'[DiaryTime])-SUM('fact BookedTime'[BookingHours]))<=50)))

     RETURN

SUMX(availablehours,[Available Hours])

I'm trying to implement my What If Parameter now though. My What If Parameter created a table with a column and the value, but it seems as though where I have <= in the above dax I can't plug in the What If Parameter column. I can only input the value, but the parameter when pulled in as a slicer is a column and cannot be a measure.

Is there a way to replace that <=50 with the parameter column???