基于度量值的PowerBI动态装箱(范围更改) [英] PowerBI Dynamic binning (ranges change) based on value of measure

问题描述

我正在尝试通过装箱表示一些连续的数据.区域的连续加权数据应归类为:VeryHigh，High，Low，VeryLow.权重值基于按区域分组的某些事件类型之间的相互作用，因此可以根据报表用户选择的类型进行更改.

我在下面提供了一些示例数据，并概述了到目前为止所做的事情.

从五组区域数据(A-E)开始.在每个事件中都有一个或多个事件类型.每个事件都有一个权重及其在区域内发生的次数(计数).

添加计算列CC_ALL_WGT(权重*计数)

创建度量:

  M_WGT = DIVIDE(SUM(sample_data [CC_ALL_WGT])，SUM(sample_data [4_count])) 

按区域分组后，这很有意义，我们可以看到该区域获得了总体权重得分

这可以通过根据我们要检查的事件类型对数据进行切片来更改:

我们还可以设置其他措施来获取最低限度；最大限度;基于类型"选择的度量"中位数:

  M_MIN_M_WGT = IF(countrows(values(sample_data [1_area]))= 1，sample_data [M_WGT]，MINX(值(sample_data [1_area])，sample_data [M_WGT])) 

做出切片器选择后会发生预期的变化

还设置了一种度量，以确定最小值和中值之间的中点以及最大值和中值之间的中点

  M_MidMinMed =sample_data [M_MED_M_WGT]-((sample_data [M_MED_M_WGT]-sample_data [M_MIN_M_WGT])/2) 

我想使用这些值来创建基于以下内容的条带:

非常低:(最低至MinMed中点)低:(最低至最低)高:(中值至MedMax中点)VeryHigh :(从MedMax到最大值)

因此，基于以下选择

垃圾箱的设置如下

1. 非常低(0.59至0.76)
2. 低(0.76至0.93)
3. 最高(0.93至1.01)
4. 非常高(1.01至1.1)

A区将位于Bin 4(非常高)中；Bin 2(低)中的B区;Bin 1(非常低)中的C区;Bin 2中的D区(低)；Bin 4中的E区(非常高)

如果选择特定类型(通过切片器)查看，则垃圾箱将设置如下:

1. 非常低(0.35至0.61)
2. 低(0.61至0.88)
3. 最高(0.88至1.06)
4. 非常高(1.06至1.24)

因此检查M_WGT(具有切片器中指定的类型):

A区将位于Bin 4(非常高)中；Bin 2(低)中的B区;Bin 1(非常低)中的C区;Bin 1(非常低)中的D区;箱4中的E区(高)

注意-D区的垃圾箱分类从低到非常低

这就是我被困住的地方.这篇文章指定了如何应用静态bin范围:

然后我可以将垃圾箱与视觉效果相关联时，它有很多问题.首先，装仓发生在TYPE级别而不是AREA级别.其次，我要手动设置范围值.

当我说类型"级别时，我的意思是说他们正在此级别进行分类:

我希望直方图表示的是Area级别的M_WGT值.

如果我按区域A切片，则问题更容易发现:

我想要在直方图中有一个表示A区域的表示(1.10的bin)，而不是当前显示的三个(对于每个1.9型； 1； 0.35)

希望我已经设法传达了问题和要求.

赞赏任何建议或见识.

链接到报告+数据源在这里: https://www.dropbox.com/sh/oganwruacdzgtzm/AABlggr3-xqdMvPjuR9EyrMaa?dl = 0

解决方案

您可以通过单个度量来定义区域的存储桶:

 桶=VAR权重=汇总(ALLSELECTED(sample_data)，sample_data [1_area]，"Wgt"，[M_WGT])VAR MinW = MINX(权重[Wgt])VAR MaxW = MAXX(权重[Wgt])VAR MedW = MEDIANX(权重[Wgt])VAR MinMedW =(MinW + MedW)/2VAR MedMaxW =(MedW + MaxW)/2VAR CurrW = CALCULATE([M_WGT]，ALLSELECTED(sample_data [2_type]))返回转变 (真的 ()，CurrW< = MinMedW，"1_VeryLow"，CurrW< = MedW，"2_Low"，CurrW< = MedMaxW，"3_High"，CurrW< = MaxW，"4_VeryHigh") 

这总结了过滤器选择( ALLSELECTED )中所有内容的权重，然后根据您的指定定义边界.然后，我们计算所有选定类型的当前区域的权重，并将其传递到开关中，在此检查值从低到高.

---

现在您不能将度量用作图表的轴，因此，如果要将这些存储桶都放在轴上，建议您定义一个独立的表.

 范围=数据表(范围"，STRING，{{"1_VeryLow"}，{"2_Low"}，{"3_High"}，{"4_VeryHigh"}}) 

在轴上放置 Ranges [Range] 并适当地定义计数度量.

  CountArea =COUNTROWS(FILTER(sample_data，[Range] = SELECTEDVALUE(Ranges [Range]))) 

我真的不知道您要计算的是什么，应该算是一个不同的计数，还是不应该涉及到 4_count ，但是需要根据需要修改此计数方法.

>

I’m trying to represent some continuous data via binning. Continuous weighting data of an area should be binned as: VeryHigh, High, Low, VeryLow. The weighting values are based on an interaction between certain Types of events grouped by an Area and so can change depending on the Type selected by the report user.

I have included some sample data below and an outline of what’s been done so far.

Start with five sets of area data (A-E). Within each is one or more incident Types. Each incident has a Weighting and the number of times (Count) it occurs within the Area.

Add a calculated column CC_ALL_WGT (weighting * count)

Create a measure:

M_WGT = DIVIDE(SUM(sample_data[CC_ALL_WGT]), SUM(sample_data[4_count]))

This makes sense once grouped by Area and we can see that the Area gets an overall Weighting Score

This can be altered by slicing the data based on which Type of incident we wish to inspect:

We can also set up additional measures to get the Min; Max; Median from the Measure based on the Type selection:

M_MIN_M_WGT = IF(
    countrows(values(sample_data[1_area])) = 1,
    sample_data[M_WGT],
    MINX(
        values(sample_data[1_area]),
        sample_data[M_WGT]
    )
)

Which change as expected when a Slicer selection is made

Also set up a measure to determine the Mid-Point between the Minimum and the Median and Mid-Point between the Maximum and the Median

M_MidMinMed =
sample_data[M_MED_M_WGT] - ((sample_data[M_MED_M_WGT] - sample_data[M_MIN_M_WGT]) / 2)

What I would like to do with these values is create a banding based on the following:

VeryLow: (Minimum to MinMed mid-point) Low: (MinMed to Median) High: (Median to MedMax mid-point) VeryHigh: (MedMax to Maximum)

So based on the following selection

The bins would be set up as follows

1. VeryLow (0.59 to 0.76)
2. Low (0.76 to 0.93)
3. High (0.93 to 1.01)
4. VeryHigh (1.01 to 1.1)

Area A would be in Bin 4 (VeryHigh); Area B in Bin 2 (Low); Area C in Bin 1 (VeryLow); Area D in Bin 2 (Low); Area E in Bin 4 (VeryHigh)

If select specific Types to review (via the slicer) the bins would be set up as follows:

1. VeryLow (0.35 to 0.61)
2. Low (0.61 to 0.88)
3. High (0.88 to 1.06)
4. VeryHigh (1.06 to 1.24)

So checking M_WGT (with types specified in the slicer):

Area A would be in Bin 4 (VeryHigh); Area B in Bin 2 (Low); Area C in Bin 1 (VeryLow); Area D in Bin 1 (VeryLow); Area E in Bin 4 (High)

NOTE - The change in bin classification for Area D from Low to VeryLow

This is where I get stuck. This post specifies how to apply a static bin range: https://community.powerbi.com/t5/Desktop/Histogram-User-defined-bin-size/m-p/69854#M28961 but I’ve not been able to do this using dynamic or changing values (the Min; Max; Media; Midpoint) depending on selection.

The closest I’ve managed to apply is as follows:

Range =
VAR temp =
    CALCULATE ( sample_data[M_WGT] )
RETURN
    IF (
        temp < 0.76,
        "1_VeryLow",
        IF (
            AND ( temp > 0.76, temp <= 0.93 ),
            "2_Low",
            IF (
                AND ( temp > 0.93, temp <= 1.01 ),
                "3_High",
                "4_VeryHigh"
            )
        )
    )

Which permitted the following:

While I can then associate the Bins with a visual there are a number of things wrong with it. Firstly binning is occurring at the TYPE level not the AREA level. Secondly I’m manually setting the range values.

When I say Type levels what I mean is that they’re being binned at this level:

Whereas what I would like the histogram to be representing are the M_WGT values at the Area level.

If I slice by Area A only the problem is easier to see:

What would I like is for there to be one representation of Area A in the histogram (the bin for 1.10), not the three currently being shown (for each Type 1.9; 1; 0.35)

Hopefully I’ve managed to convey the problem and requirement.

Appreciate any advice or insight.

EDIT: Link to Report + Data source is here: https://www.dropbox.com/sh/oganwruacdzgtzm/AABlggr3-xqdMvPjuR9EyrMaa?dl=0

解决方案

You can define the bucket for an area all in a single measure:

Bucket = 
VAR Weights =
    SUMMARIZE ( ALLSELECTED ( sample_data ), sample_data[1_area], "Wgt", [M_WGT] )
VAR MinW = MINX ( Weights, [Wgt] )
VAR MaxW = MAXX ( Weights, [Wgt] )
VAR MedW = MEDIANX ( Weights, [Wgt] )
VAR MinMedW = ( MinW + MedW ) / 2
VAR MedMaxW = ( MedW + MaxW ) / 2
VAR CurrW = CALCULATE( [M_WGT], ALLSELECTED( sample_data[2_type] ) )
RETURN
    SWITCH (
        TRUE (),
        CurrW <= MinMedW, "1_VeryLow",
        CurrW <= MedW,    "2_Low",
        CurrW <= MedMaxW, "3_High",
        CurrW <= MaxW,    "4_VeryHigh"
    )

This summarizes the weights over everything within your filter selections (ALLSELECTED) and then defines your boundaries as you specified. Then we calculate the weight for the current area across all selected types and pass that into the switch where we check the values from low to high.

---

Now you can't use a measure as an axis for a chart so if you want these buckets on the axis, I'd recommend defining an independent table.

Ranges =
DATATABLE (
    "Range", STRING,
    {
        { "1_VeryLow" },
        { "2_Low" },
        { "3_High" },
        { "4_VeryHigh" }
    }
)

Put Ranges[Range] on the axis and define a counting measure as appropriate.

CountArea =
COUNTROWS ( FILTER ( sample_data, [Range] = SELECTEDVALUE ( Ranges[Range] ) ) )

I don't really know what you're trying to count, whether it should be a distinct count, or if 4_count should be involved or not but modify this counting measure as necessary.

这篇关于基于度量值的PowerBI动态装箱(范围更改)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！