cancel
Showing results for
Did you mean:
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:

 STARTDATE D_T RANGE_DE_TX_DL BAIN VALUE 2017-09-10 35 0 Kbps~300Kbps D 10,85138889 2017-09-10 -1 300Kbps~600Kbps NULL 39,34027778 2017-09-10 27 600Kbps~1Mbps B 81,405 2017-09-10 31 1Mbps~2Mbps B 220,3291667 2017-09-13 14 2Mbps~4Mbps C 20,18055556 2017-09-14 18 4Mbps~6Mbps B 64,82083333 2017-09-14 22 6Mbps~10Mbps B 52,144 2017-09-15 21 10Mbps~15Mbps A 20,523 2017-09-15 79 >15Mbps C 15,26111111 2017-09-15 11 600Kbps~1Mbps A 359,9986111 2017-09-15 21 0 Kbps~300Kbps A 23 2017-09-16 11 0 Kbps~300Kbps A 628,7347222 2017-09-16 94 300Kbps~600Kbps C 10,66944444 2017-09-16 35 600Kbps~1Mbps B 708,8361111 2017-09-16 50 1Mbps~2Mbps D 50

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

 MIN_DATE MAX_DATE 2017-09-10 2017-09-16 0 Kbps~300Kbps 10,85138889 628,7347222 300Kbps~600Kbps 39,34027778 10,66944444 600Kbps~1Mbps 81,405 708,8361111 1Mbps~2Mbps 220,3291667 50

Is it possible to do this?

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I

## Re: Summarize / Lookup Data

Hi,

It follows again:

For the first date in the filter context:

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

Regards,

5 REPLIES 5
Highlighted
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.

Best regards,

Highlighted
Resolver I

## Re: Summarize / Lookup Data

Hi,

It follows again:

For the first date in the filter context:

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

Regards,

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~300Kbps 10,85138889 3,08% 300Kbps~600Kbps 39,34027778 11,18% 600Kbps~1Mbps 81,405 23,13% 1Mbps~2Mbps 220,3291667 62,61% 351,9258333 100%
Highlighted
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

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

Thanks again.