如何计算销售范围内的排名 [英] How to calculate rank within Sales ranges

问题描述

如何在销售级别定义的类别内计算排名。假设我们要为销售额标签的产品在高类别下高于某个阈值，并在低类别下低于该阈值。

这里是示例数据。

  let 
源= Table.FromRows（Json.Document（Binary.Decompress（Binary.FromText（ i45WcisqzSwpVtJRSiwoyEkF0oZ​​KsTpIwkmJeUJZigigQQYQSYQJYQYQSQQYQS SWJJPtwJKHL5eZn5eUDaHNUqHI5GdkEsAA ==，BinaryEncoding.Base64），Compression.Deflate）），在类型表[类别= _t，产品= _t，数量= _t]中的_t =（（类型文本）meta [Serialized.Text = true]） ，
＃更改的类型 = Table.TransformColumnTypes（Source，{{ Amount，Int64.Type}}）
 in 
＃更改的类型 
  

我的问题是与

参考

以下是我在陈述该问题时遇到的一系列相关问题。

每个类别的DAX相当于Excel PERCENTRANK.INC

类别中的DAX RANKX

DAX REMOVEFILTERS与ALL

DAX函数中的value参数RANKX

DAX ALLEXCEPT可以按多维表的类别进行汇总

解决方案

非常感谢Alexis Olson，我想分享一个最终得到的不同解决方案。 Alexis提出的解决方案在我的简单示例中效果很好，但在我的复杂模型中却没有效果。在我的复杂模型中，RANKX函数无法提供预期的结果。 RANKX针对不同的销售价值返回相同的排名。

目前，这是一种有效的解决方案，无需弄清楚是什么原因导致RANKX为不同的销售价值返回联系。

首先，定义类别度量：

  CalculatedCategory = 
 SWITCH（
 TRUE（），
 NOT（HASONEVALUE（MyTable [Product]）），总计，-对于总计
的重要处理[销售]< = 4， 低，
 [销售]> 4，高，
其他 
）
  

从类别中排除总计很重要。我通过设置总计的其他类别来做到这一点。否则，总数将属于高类别类别。这会扭曲最终结果。

我在计算类别内的百分比排名时没有使用RANKX。我使用了MIXTURE OF COUNTROWS和FILTER。

 类别内的百分比排名= 
 VAR类别= [CalculatedCategory] ​​
 VAR ProductSales = [Sales] 
 VAR产品匹配= 
 COUNTROWS（
 FILTER（
 ALLSELECTED（MyTable [Product]），
 [CalculatedCategory] ​​=类别
&& [销售]> ; = ProductSales 
）
）
 var ProductsAll = 
 COUNTROWS（
 FILTER（
 ALLSELECTED（MyTable [Product]），
 [CalculatedCategory ] =类别
）
）
 
返回
除（ProductsMatching-1，MAX（ProductsAll-1，1））
  

我计算了两个表的行。第一个表 ProductsMatching 包含具有适当类别销售额和高于或等于该产品销售额的所有产品。 ProductsAll 返回类别中的产品数。

How to calculate rank within Category defined on sales level. Say, that we want to label products with Sales above some threshold with Category "high", and below that threshold with Category "low".

Here is a sample data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcisqzSwpVtJRSiwoyEkF0oZKsTpIwkmJeUAIZJigipfn56QlpRYVVQLZpqhSyRlQcWOweFhqempJYlJOKlgusagovwS7XEF+SWJJPtwJKHL5eZn5eUDaHNUqHI5GdkEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, Product = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}})
in
    #"Changed Type"

My question is a nephew related to its older uncle, who now I want to call in:

Percent Rank within Category = 

VAR HasOneValueTrue = HASONEVALUE ( MyTable[Product] )

VAR tbl =
    CALCULATETABLE (
        VALUES ( MyTable[Product] ),          
        REMOVEFILTERS ( MyTable[Product] ),   
        VALUES ( MyTable[Category] )          
    )

VAR result =
    CALCULATE (
        DIVIDE (
            RANKX (
                tbl,
                [Sales], 
                ,
                ASC
            ) - 1,
            COUNTROWS ( tbl ) - 1
        )
    )
RETURN 
    IF (
        HasOneValueTrue,
        result
    )

The difference is that the uncle has Category defined in table column, but now we want to have category calculated on the fly based on sales level. So I tried replacing the VAR tbl code with the following one with the threshold of 4:

var tbl =
SUMMARIZECOLUMNS (
    MyTable[Product],
    "CalculatedCategory", IF ( [Sales] > 4, "high", "low" ),
    "AggSales", [Sales]
)

Nevertheless, I am not able to refer to such defined variable. I also failed with trial based on creating first a DAX table and then trying to refer to it.

Here are expected results:

References

Here is the family of related questions which members I met on the way while approaching to state this problem.

DAX equivalent of Excel PERCENTRANK.INC per category

DAX RANKX for within Category

DAX REMOVEFILTERS vs ALL

The value parameter in DAX function RANKX

DAX ALLEXCEPT to sum by category of multiple dimension tables

解决方案

Being very grateful to Alexis Olson, I would like to share a different solution I ended up with. The solution proposed by Alexis works well in my simple example, but it did not work in my complex model. In my complex model the RANKX function does not give the expected results. RANKX returns the same rankings for different sales values.

For the time being this is the solution that works without figuring out what causes RANKX to return ties for different sales values.

First of all, defining Category measure:

CalculatedCategory = 
SWITCH (
    TRUE (),
    NOT ( HASONEVALUE ( MyTable[Product] ) ), "total", -- important to handle totals
    [Sales] <= 4, "low",
    [Sales] >  4, "high",
    "other"
)

It is important to exclude totals from Category. I did it by setting up a different category for totals. Otherwise totals will fall into "high" category bucket. It would distort final results.

I have not used RANKX in calculation of Percent Rank within Category. I used MIXTURE OF COUNTROWS and FILTER.

PercentRank within Category = 
VAR category = [CalculatedCategory]
VAR ProductSales = [Sales]
VAR ProductsMatching = 
COUNTROWS (
            FILTER (
                ALLSELECTED ( MyTable[Product] ),
                [CalculatedCategory] = category
                    && [Sales] >= ProductSales
            )
        )
var ProductsAll = 
COUNTROWS (
            FILTER (
                ALLSELECTED ( MyTable[Product] ),
                [CalculatedCategory] = category
            )
        )

RETURN
    DIVIDE (ProductsMatching-1, MAX( ProductsAll-1, 1 ))

I calculated rows of two tables. First table ProductsMatching has all products that have sales in appropriate category and sales that are higher or equal of the product. ProductsAll returns number of products in category.

这篇关于如何计算销售范围内的排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！