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

Summarize / Lookup Data

Hello everyone.

 

I'm starting my studies at PowerBi, and I came up with a challenge that I can not solve.

I need to summarize a table, grouping by a category, searching the first date of this category, according to the filters and with this category / date fetch the value at that time.

 

Example:

 

My table: 

STARTDATED_TRANGE_DE_TX_DLBAINVALUE
2017-09-10350 Kbps~300KbpsD10,85138889
2017-09-10-1300Kbps~600KbpsNULL39,34027778
2017-09-1027600Kbps~1MbpsB81,405
2017-09-10311Mbps~2MbpsB220,3291667
2017-09-13142Mbps~4MbpsC20,18055556
2017-09-14184Mbps~6MbpsB64,82083333
2017-09-14226Mbps~10MbpsB52,144
2017-09-152110Mbps~15MbpsA20,523
2017-09-1579>15MbpsC15,26111111
2017-09-1511600Kbps~1MbpsA359,9986111
2017-09-15210 Kbps~300KbpsA23
2017-09-16110 Kbps~300KbpsA628,7347222
2017-09-1694300Kbps~600KbpsC10,66944444
2017-09-1635600Kbps~1MbpsB708,8361111
2017-09-16 501Mbps~2Mbps D50

 

 Result, if no filter is applied (D_T = All; BAIN = All)

 MIN_DATEMAX_DATE
 2017-09-102017-09-16
0 Kbps~300Kbps10,85138889628,7347222
300Kbps~600Kbps39,3402777810,66944444
600Kbps~1Mbps81,405708,8361111
1Mbps~2Mbps220,329166750

 

Is it possible to do this?


Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: Summarize / Lookup Data

Hi,

It follows again:

 

For the first date in the filter context:

CALCULATE(SUM(VALUE);FIRSTDATE(STARTDATE))

 

Regards,

View solution in original post

5 REPLIES 5
Highlighted
Resolver I
Resolver I

Re: Summarize / Lookup Data

Hi @finan,

 

Try to do FIRSTDATE function.

https://msdn.microsoft.com/en-us/library/ee634806.aspx

 

It works with filter context. 

 

I hope help you.

Best regards,

Highlighted
Resolver I
Resolver I

Re: Summarize / Lookup Data

Hi,

It follows again:

 

For the first date in the filter context:

CALCULATE(SUM(VALUE);FIRSTDATE(STARTDATE))

 

Regards,

View solution in original post

Highlighted
New Member

Re: Summarize / Lookup Data

@Thyago_Rezende Amazing...

 

I got it.

 

One more question.
Can I make a column with a% of the total value?

 

Example:

 2017-09-10%
0 Kbps~300Kbps10,851388893,08%
300Kbps~600Kbps39,3402777811,18%
600Kbps~1Mbps81,40523,13%
1Mbps~2Mbps220,329166762,61%
 351,9258333 100%
Highlighted
Resolver I
Resolver I

Re: Summarize / Lookup Data

Good!!

 

you can use quick measures for get % of total.

 

https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures

 

Best regards,

Highlighted
New Member

Re: Summarize / Lookup Data

@Thyago_Rezende 

 

I did, I changed the field display as % of grand total.

 

Thanks again.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors