Average calculation issue with Direct Query mode

[Anonymous]

Hello,

I'm currently working on Sprinklr data exploitation in PowerBI by using the dedicated Spark connector (DirectQuery mode). All is going fine except for duration average calculation.

I'm trying to reproduce this data card from Sprinklr dashboard:

I found the correct Case Duration column (default format is decimals by the way) in my dataset but when I try to use it PowerBI:

And the sum is returning 965,18bn !

As I'm forced to use DirectQuery mode, I can't see the data I'm trying to manipulate...

If any of you already encountered the same issue or maybe have an idea, it would be very helpful 🙂

Thank you.

Have a great day,

Ben

[Anonymous]

Hello,

Thanks for trying to help 🙂

I adapted the measure and ran it, I got no error but I still have 0 as a result:

I think that the problem is at the root when calculating average variable...

The strange behaviour is that I can calculate the sum (so there's data) but not the average, and count is returning (Blank) value.

Let's see if someone has an idea to share 🤞

Have a nice day

[changqing]

Hi @Anonymous ,

Would you please share the formula of TIME MEASURE? 

Is there row-level security?

And please check the modeling-limitations

Best Regards,
changqing

[Anonymous]

Hello changqing, thanks a lot for helping 🙂

The formula was basically the one proposed by amitchandak but the average calculation on the first line was returning 0 in all cases.

There's no row-level security.

I took a look at the modeling limitations and I don't really see something that could avoid me to calculate the average.

That's very frustrating to don't be able to see the raw data behind...

Best regards

[Anonymous]

Hi @Anonymous,

You can take a look at the following blog about aggregation on duration if it help with your scenairo:

Aggregating Duration Tim 

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , If you duration is minute or second

Meausre =

var _avg = average(Table[Duration])

return

Quotient([_avg ],86400) & " Day :" Quotient(mod([_avg],86400),3600) &" Hours :" & Quotient(mod(mod([_avg ],86400),3600),60) &" Minute :" & mod(mod(mod([_avg ],86400),3600),60)