Forum Discussion
Partition by logic in PowerBI
Hi Can someone help me to replicate this SQL logic into PowerBI.
DATA:
| Company | date | Detail RID | BIN RID | LIN RID | Detail sum | Bin SUM | LIN SUM |
| 100 | 9/22/2022 | A123 | B222 | L111 | 10 | 30 | 30 |
| 100 | 9/22/2022 | A124 | B223 | L111 | 10 | 30 | 30 |
| 100 | 9/22/2022 | A125 | B224 | L111 | 10 | 30 | 30 |
Current OUTPUT IN POWERBI:
| Company | date | Detail sum | Bin SUM | LIN SUM |
| 100 | 9/22/2022 | 30 | 90 | 90 |
Expected OUTPUT IN POWERBI :
| Company | date | Detail sum | Bin SUM | LIN SUM |
| 100 | 9/22/2022 | 30 | 30 | 30 |
When i am taking data to powerBI, The Detail Sum id showing 30 and BIN and LIN also showing the sum based on their ID.
Please check above mentioned data and output. Help me to get right output by implementing parition logics in powerbi. I want take the sum values ONLY for highest value of the BIN and LIN ID.
I TRIED IN SQL, But i dont know how to implement in PowerBI.
Thank you
Hi Abale,
Did the data table bin and lin field value aggregate with sum mode? If that is the case, you need to write a formula to use a variable with summarize function to aggregate table records at first.
Then you can use the iterator function maxx to aggregate the variable table to get the max values from the summarized bin and link field values.Measure Totals, The Final Word
If not, you can directly right-click on these fields to use 'max' mode to aggregate the bin and lin field values.
Regards,
Xiaoxin Sheng
1 Reply
Hi Abale,
Did the data table bin and lin field value aggregate with sum mode? If that is the case, you need to write a formula to use a variable with summarize function to aggregate table records at first.
Then you can use the iterator function maxx to aggregate the variable table to get the max values from the summarized bin and link field values.Measure Totals, The Final Word
If not, you can directly right-click on these fields to use 'max' mode to aggregate the bin and lin field values.
Regards,
Xiaoxin Sheng