Using a measure for Bucketing data

[JK_29]

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.

[lbendlin · Accepted solution]

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.

[v-easonf-msft]

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 _ Eason

[JK_29]

Hi,

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])))

[v-easonf-msft · Accepted solution]

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

[lbendlin]

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

[JK_29]

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?

[lbendlin · Accepted solution]

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.