Forum Discussion
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.
Hi,
It follows again:
For the first date in the filter context:
CALCULATE(SUM(VALUE);FIRSTDATE(STARTDATE))
Regards,
5 Replies
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,
Hi,
It follows again:
For the first date in the filter context:
CALCULATE(SUM(VALUE);FIRSTDATE(STARTDATE))
Regards,
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% Good!!
you can use quick measures for get % of total.
https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures
Best regards,
