cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lsgit New Member
New Member

Aggregation of data from MSSQL Query

Hello,

 

can anybody help me with a possibly simple question:

 

From a merchandise management system for rental articles, I have a database query that lists which article leaves the warehouse on which day and reaches it again.
This results in at least two rows per article for the respective event. In addition, the article can of course also be planned for several events in the given period, so that it appears several times in the query result. In addition to the columns 'quantity', 'itemname', 'event', 'date of loading' and 'date of return', there is also a column 'time requirement', which specifies the preparation and postprocessing time for an article.

Now I would like to determine in PowerBI how many time units are required for each loading and unloading day. So, for example, you want to analyze which articles leave the warehouse in which quantity on day x and calculate the total accordingly.


Example:

 

<itemname>  | <quantity> | <event>       | <date of loading>   | <date of return>    | <time requirement> | <task>

---------------------------------------------------------------------------------------------------------------------
Surface Book| 5          |  Kickoff 2020 | 2020-02-05 10:00:00 | 2020-02-09 08:00:00 | 15                 | loading
Surface Book| 5          |  Kickoff 2020 | 2020-02-05 10:00:00 | 2020-02-09 08:00:00 | 15                 | unloading
Headphones  | 15         |  Kickoff 2020 | 2020-02-05 10:00:00 | 2020-02-09 08:00:00 | 15                 | loading
Headphones  | 15         |  Kickoff 2020 | 2020-02-05 10:00:00 | 2020-02-09 08:00:00 | 15                 | unloading
...
Surface Book| 2          | Conferency xy | 2020-02-05 16:00:00 | 2020-02-14 06:00:00 | 15                 | loading
Surface Book| 2          | Conferency xy | 2020-02-05 16:00:00 | 2020-02-14 06:00:00 | 15                 | unloading
...
Surface Book| 2          | Exhibition xy | 2020-03-01 11:00:00 | 2020-03-07 06:00:00 | 15                 | loading
Surface Book| 2          | Exhibition xy | 2020-03-01 11:00:00 | 2020-02-07 06:00:00 | 15                 | unloading
...


The result should be something like:

 

On 5th of february we need (5 x 15) + (15 x 15) + (2 x 15) =  330 time units for loading...

 

I hope I was able to express myself reasonably well.


Regards

Lars

3 REPLIES 3
Super User IV
Super User IV

Re: Aggregation of data from MSSQL Query

@lsgit add following measures 

 

Quantity = SUM ( Table[Quantity] )

Time Required = MAX ( Table[Time Required] )

Loading Time = 
CALCULATE ( [Quantity] * [Time Required], Table[Task] = "Loading" )

Unloading Time = 
CALCULATE ( [Quantity] * [Time Required], Table[Task] = "Unloading" )

You can use loading and unloading time measure in your visuals.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Community Support Team
Community Support Team

Re: Aggregation of data from MSSQL Query

Hi @lsgit ,

 

we can use the following two measure to meet your requirement.

 

loadingTimes = 
SUMX ( FILTER ( 'Table', [task] = "loading" ), [quantity] * [time requerement] )
unloadingTimes = 
SUMX ( FILTER ( 'Table', [task] = "unloading" ), [quantity] * [time requerement] )

16.PNG

 


If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Aggregation of data from MSSQL Query

Hi @lsgit ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors