如何根据多过滤器报告显示上一个销售期间(会计年度) [英] 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, ...

Dropbox 上可用的示例文件:

Example files avaliable on dropbox:

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

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

On my closest attempt, i tried this follow:

在我的数据集上添加一列，将每个时期命名为不同的数字，例如:17"；18"；19"，由于财政年度(4 月至 3 月)错位.

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屋！