Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi,
It follows again:
For the first date in the filter context:
CALCULATE(SUM(VALUE);FIRSTDATE(STARTDATE))
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,
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |