Disconnected table measure not grouping as expected

macfuller

Active Member
Joined
Apr 30, 2014
Messages
256
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:

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
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.
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
256
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.
 

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
Right. Filter the main table rather than ap interval table then.

Look up static segmentation or dynamic segmentation on SQLBI.com.
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
256
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.
 

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
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.
 

Forum statistics

Threads
1,085,306
Messages
5,382,834
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top