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
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors