如何根据多重过滤器报告显示销售的最后期间(会计年度) [英] How to Show last period (Fiscal Year) of sale, based on multi-filters report

问题描述

我尝试根据会计年度创建一个仪表板，其中包含更多过滤器，例如地区，销售代表名称，...

I tried create a dashboard based on fiscal year, with more Filters, like region, sales rep name, ...

在保管箱上可以找到的示例文件:

Example files avaliable on dropbox:

https://www.dropbox.com/sh/l25kdz6enmg35yb/AABPuOk3kf?dl = 0

在我最接近的尝试中，我尝试了以下操作:

On my closest attempt, i tried this follow:

在我的数据集上添加一列，由于会计年度(4月至3月)被撤消，因此将每个期间分别命名为不同的数字，例如:"17";"18";"19".

Add one column on my data set, naming each period as distinct number, like: "17";"18";"19", due to deslocated fiscal year (april to march).

然后创建一个度量:

PREVIOUS CROP_YEAR = SWITCH(TRUE();
SELECTEDVALUE('dataset'[Crop-X])=16;(CALCULATE(SUM('dataset'[Order Value]);ALL('dataset')));
SELECTEDVALUE('dataset'[Crop-X])=17;(CALCULATE(SUM('dataset'[Order Value]);ALL('dataset')));
SELECTEDVALUE('dataset'[Crop-X])=18;(CALCULATE(SUM('dataset'[Order Value]);ALL('dataset')));
SELECTEDVALUE('dataset'[Crop-X])=19;(CALCULATE(SUM('dataset'[Order Value]);ALL('dataset')));
0)

预期输出为:基于所应用的所有过滤器的值，但是相反，我只是得到了一个空图表

Expected output was: Values based on all filters applied, But instead i just get an empty charts

推荐答案

该度量返回总计，因为您通过使用ALL函数明确要求该总计.这将从数据集中删除所有过滤器，从而返回总计.这是可行的，但由于具有两个时间维度，因此在数据集中产生了复杂性.解决此问题的方法是，首先确保针对两个维度正确地过滤日期

The measure is return the total because you are explicitly asking for it by using the ALL function. This removes all the filters from the dataset thus returning a grand total. This can work but it creates a complexity in your dataset with respect of having two time dimensions. The way to solve this is to first make sure you filter the date correctly with respect to both dimensions

PREVIOUS YEAR = 
    CALCULATE(
        SUM('dataset'[Order Value]);
        FILTER( 
            ALL ( 'dataset' ) ;
            AND ( 
                'dataset'[Crop-X] = MAX('dataset'[Crop-X]) -1 ;
                'dataset'[YEAR] = MAX('dataset'[YEAR] ) -1
            )
        )
    )

此外，此度量仍然使用ALL函数，这意味着所有其他过滤器都将被忽略.相反，使用ALLSELECTED会导致相对时间过滤，一旦您在仪表板中选择了任何基于时间的切片器，就不会产生任何结果，这将阻止过滤器查看不在主要切片数据集中的数据集的任何其他部分.解决方法是使用ALLEXCEPT并添加要用作参数的过滤器.不利之处在于，您添加到信息中心的所有过滤器都必须手动添加到例外中.

Furthermore, this measure still uses the ALL function which means any other filters get ignored. Using ALLSELECTED instead would result in the relative time filtering to result in nothing as soon as you select any time based slicer in your dashboard, this prevents the filter from looking at any other part of the dataset that is not within the primary sliced dataset. The workaround would be to use ALLEXCEPT and add the filters you want to be able to use as arguments. Downside is that any filter you add to your dashboard will have to be added to the exception manually.

PREVIOUS YEAR = 
    CALCULATE(
        SUM('dataset'[Order Value]);
        FILTER( 
            ALLEXCEPT( 'dataset' ; Dim1[Group] ; Dim1[Manager] ; Dim1[Region] ) ;
            AND ( 
                'dataset'[Crop-X] = MAX('dataset'[Crop-X]) -1 ;
                'dataset'[YEAR] = MAX('dataset'[YEAR] ) -1
            )
        )
    )

这篇关于如何根据多重过滤器报告显示销售的最后期间(会计年度)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！