cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
David283
Frequent Visitor

Trouble with IFF and SUM of multiple lines in a group

Hello all, I am new to paginated reports. 

I am working on a time report and having a weird issue. I am building a report that lists each employee and their time, grouped by date and location.  Each person will have a number of hours worked over several days, and the times can have different types such as REG, OT, VAC and such.  I then want to do totals for each type of hours for each employee and grand totals at the end.

 

Here is what my design view looks like :

 

David283_0-1655910288161.png

 

The expressions above are  

=Sum(IIF(Fields!cr242_typeworked.Value="REG",Fields!cr242_hoursworked.Value,0))

=Sum(IIF(Fields!cr242_typeworked.Value="OT",Fields!cr242_hoursworked.Value,0))

And so on.  The totals below are the same expressions.

 

The problem I am having is that when I run the report, every thing works fine if the employee ony has one type of time for the report date range.  So if all their time is REG or VAC then the totals work.  But if they have a mix of time types, like REG and OT, then those sums show as errors.

 

Sample output =

David283_1-1655910900116.png

 

As you can see, the second employee worked fine, but the first one did not.  Any guess what is causing this?

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @David283,

I'm not so sure about the corresponding row value data types, perhaps you can try to add the VAL function to convert these values to numbers before aggregating.

Val Function (microsoft.com)

=Sum(VAL(IIF(Fields!cr242_typeworked.Value="REG",Fields!cr242_hoursworked.Value,0)))

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
David283
Frequent Visitor

Wow, ok, I have no idea why that worked, but it did.  Thank you very much!

v-shex-msft
Community Support
Community Support

Hi @David283,

I'm not so sure about the corresponding row value data types, perhaps you can try to add the VAL function to convert these values to numbers before aggregating.

Val Function (microsoft.com)

=Sum(VAL(IIF(Fields!cr242_typeworked.Value="REG",Fields!cr242_hoursworked.Value,0)))

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors