Forum Discussion
Using a measure for Bucketing data
I have two tabular sets as listed below, table A and table B. Both the tables are connected using column assignment in table A and column Billing Doc in table B.
Table A
| ID | Doc | Document Date | Amount | Assignment |
| 200010 | 10007247 | 01-02-2022 | 50,000 | |
| 200010 | 10007248 | 01-02-2022 | 3,40,959 | 60024141 |
| 200010 | 9000160 | 23-07-2022 | 7,34,745 | 60024031 |
| 200016 | 10004826 | 01-07-2022 | 3,84,74,875 | 60024475 |
| 200016 | 14000028 | 26-07-2022 | 34,000 | 60024475 |
Table B
| Billing Doc | Date | ID | P Doc | Realisable value |
| 60024141 | 21-04-2022 | 200010 | 70231020 | 3500000 |
| 60024475 | 17-05-2022 | 200016 | 73293000 | 40000000 |
| 60024475 | 17-05-2022 | 200016 | 73293000 | 40000000 |
| 60024475 | 17-05-2022 | 200016 | 73293000 | 40000000 |
| 60024475 | 17-05-2022 | 200016 | 73293000 | 40000000 |
| 60024475 | 17-05-2022 | 200016 | 73293000 | 40000000 |
| 60024475 | 17-05-2022 | 200016 | 73293000 | 40000000 |
| 60024141 | 27-04-2022 | 200010 | 70231020 | 3500000 |
| 60024031 | 30-04-2022 | 200010 | 73827377 | 8500000 |
| 60024141 | 21-04-2022 | 200010 | 70231020 | 3500000 |
| 60024031 | 21-04-2022 | 200010 | 73827377 | 8500000 |
| 60024031 | 21-04-2022 | 200010 | 73827377 | 8500000 |
Have two measures, where one is finding a %ge value of two fields and other measure is to bucketize the first measure into various categories.
Measure 1
O/s % to P Doc = VAR RealizablevalueMax = CALCULATE(MAX(TableB[Realisable value]),FILTER(TableB,TableB[P Doc]))
Return
DIVIDE(sum(Table A[Amount in Doc Currency]),RealizablevalueMax)
Measure 2
DueBucket = IFERROR(IF([O/s % to P Doc] > .9, "Above 90% Due", IF([O/s % to P Doc] >.1, "Upto 90% due", IF([O/s % to P Doc] <.1, "Upto 10% Due",0))),0)
The final output is summarized values by Account column,
| Year | Account | P Doc | Amount in Local Currency | Realisable value | O/s % to SO | Assignment | Due Bucket |
| 2022 | 200010 | 70231020 | 340959 | 3500000 | 9.74% | 60024141 | Upto 10% Due |
| 2022 | 200010 | 73827377 | 734745 | 8500000 | 8.64% | 60024031 | Upto 10% Due |
| 2022 | 200010 | 73293000 | 38508875 | 40000000 | 96.27% | 60024475 | Above 90% Due |
However, this bucket measure is not available for use in chart. Any suggestions would help.
Your "amount in local currency" has to be a measure that is based on the logic for each item in your bucket table - whatever that logic is.
Hi， JK_29
The filter in your formula is performed in a Table called ‘measuretable’, you need replace it with fact table ’AR_details‘．
Please check the formula in the pbix file I provided before.
Best Regards,
Community Support Team _ Eason
6 Replies
Use a disconnected table with the desired bucket values. Then adjust your measure to include that table into the visual
Dynamic Segmentation/ Bucketing/ Binning - Microsoft Power BI Community
Thanks Ibendlin, I tried the solution through an independent table. However, my requirement is to sum the amount in local currency by the buckets defined. I am unable to get that since the buckets table is not linked to AR table. How do we achieve a bar chart with buckets in x axis and amount in local currency in Y axis?
Your "amount in local currency" has to be a measure that is based on the logic for each item in your bucket table - whatever that logic is.
Hi， JK_29
You need to enter a an independent table ‘Bucket’.
The ‘Bucket’ table is not linked to AR but you can create a new meausre based on these two tables.
Please add a measure like below and apply it to the x-axis.
Amount in Local Currency = CALCULATE ( SUM ( 'Table A'[Amount in Doc Currency] ), FILTER ( 'Table A', [DueBucket]=SELECTEDVALUE ( 'Due Bucket'[Bucket] )) )Best Regards,
Community Support Team _ EasonHi,
I tried using the measure for local currency amount, however, i am unable to get the graph in same fashion. What could be wrong? Here is the measure that I am using
LocalCurr.agg = CALCULATE ( SUM ( AR_details[Amount in Local Currency]), FILTER(Measuretable,[DueBucket]= SELECTEDVALUE('Bucket Sizing'[Bucket])))Hi， JK_29
The filter in your formula is performed in a Table called ‘measuretable’, you need replace it with fact table ’AR_details‘．
Please check the formula in the pbix file I provided before.
Best Regards,
Community Support Team _ Eason
