Disconnected table measure not grouping as expected

macfuller

Active Member
Joined
Apr 30, 2014
Messages
317
Office Version
  1. 365
Platform
  1. Windows
I would like help discovering why a disconnected slicer is not returning the expected data. Hopefully I’m missing something obvious.

I would like to return a payment schedule based on our Accounts Payable (AP) groupings. The days to pay bucket is the Payment Date (if one exists) - Accounting Date (what we call the Invoice Date).

Nov 2017
Dec 2017
Jan 2017
< 7 days
$$$
$$
$$
7-10 days
$
$
$$
11-14 days
$$
$$$
$$
etc

<tbody>
</tbody>

I have a disconnected table ‘AP Intervals’ with the AP text groupings along with Min and Max columns for the number of days in the group. For example
Duration
Min
Max
< 7 days
7
7-10 days
7
10
11-14 days
10
14
etc

<tbody>
</tbody>

Our vouchers table has the following structure. Note that the full check total is returned by our AP system for each row so I use the Voucher total rather than Check total.
Invoice No
Accounting Date
Voucher Amount
Check No.
Payment Date
Check Amount
A01
6/7/17
$40
001
6/20/17
$100
A02
6/12/17
$20
001
6/20/17
$100
A03
6/13/17
$40
001
6/20/17
$100
etc

<tbody>
</tbody>


These are the relevant measures I am using.

Code:
Avg Voucher Pay Days:=
[SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]AVERAGEX[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](Vouchers,[/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]IF[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1]([/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]ISBLANK[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](Vouchers[Payment Date]),[/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]BLANK[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](),Vouchers[Payment Date]-Vouchers[Accounting Date]))[/SIZE]

Code:
Paid Amt:=
[SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]CALCULATE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1]([/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]SUM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](Vouchers[Voucher Amount]),Vouchers[Check Amount]>0)[/SIZE]

Code:
Payment By Period:=CALCULATE (
    [Paid Amt],
    FILTER(
         'AP Intervals', 
         [Avg Voucher Pay Days] >= 'AP Intervals'[Min]
        && [Avg Voucher Pay Days] < 'AP Intervals'[Max]
        )
)

When I create the pivot table and use the ‘AP Intervals’ [Duration] as the row and [Payment By Period] as the value the measure returns the same total for all the AP Interval categories instead of grouping it. I'm not sure if it's something about the Average measure that is causing the difference but I've used this format in the past with no problem. Perhaps I need to group by the Check Number but I'm not sure how the measure would be created.
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
I may misunderstand what you are doing here, but I’m not sure how the last measure is meant to work. You are filtering the Ap Intervals table and then applying that to the Paid Amount Measure. But the AP intervals table is a disconnected so applying a filtered version of it to your measures won’t do anything.
 
Upvote 0
I thought it would be like applying a histogram where it counts/sums the number of items within that interval and assigns them to a particular AP "bucket". Histogram logic works along the same lines. So for items invoiced in November I would sum how many payments fell into each bucket.
 
Upvote 0
Right. Filter the main table rather than ap interval table then.

Look up static segmentation or dynamic segmentation on SQLBI.com.
 
Upvote 0
Thank you. I'm not sure how I missed that combination but it works.

Code:
Payment By Period:=CALCULATE (
    [Paid Amt],
    FILTER(
         Vouchers, 
         [Avg Voucher Pay Days] >= MAX('AP Intervals'[Min])
        && [Avg Voucher Pay Days] < MAX('AP Intervals'[Max])
        )
)

It's pretty slow though. If I create a custom column and put the Payment Date-Accounting Date into it, then use that instead of the [Avg Voucher Pay Days] measure it adjusts much faster.
 
Upvote 0
Well, if you are going to do that, you might as well put a calculated column in that calculates your categories, e.g.

Code:
<code class="dax plain">Payment by period:= <code class="dax color2">CALCULATE</code> <code class="dax parenthesis">(</code></code>
<code class="dax spaces">    </code><code class="dax color2">VALUES</code> <code class="dax parenthesis">(</code> <code class="dax plain">'AP Intervals'[Duration] </code><code class="dax parenthesis">)</code><code class="dax plain">,</code>
<code class="dax spaces">    </code><code class="dax color2">FILTER</code> <code class="dax parenthesis">(</code>
<code class="dax spaces">        </code><code class="dax plain">'AP Intervals,</code>
<code class="dax spaces">        [Avg Voucher Pay Days] >= MAX('AP Intervals'[Min])</code><code class="dax plain"> </code>
<code class="dax spaces">        </code><code class="dax plain">&& [Avg Voucher Pay Days] < MAX('AP Intervals'[Max])</code>
<code class="dax spaces">    </code><code class="dax parenthesis">)</code>
<code class="dax parenthesis">)</code>

Then you have a column you can do regular filtering by.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top