在 Power BI 中显示前 10 名时获取总数的百分比 [英] Get % of total count while displaying top 10 in Power BI

问题描述

我有一个包含 3 列日期、国家和案例编号的表格.我想显示病例最多的前 10 个国家/地区.我还想显示总数的百分比.当我选择显示值作为总计百分比的内置选项时，它会根据过滤掉的前 10 个值计算百分比.我需要的是，而不是从过滤掉的 10 条记录中计算前 10 名的百分比，它应该从时间范围内过滤的所有值中计算百分比，而不是根据案例过滤的前 10 名

I have a table with 3 columns date, country, and case numbers. I want to display the top 10 countries with the most cases. I also want to show the % of the grand total. When I select the inbuilt option of show values as %of grand total, it calculates the % based on the filtered out top 10 values. What I need is instead of calculating the % for the top 10 from the 10 records filtered out, it should calculate the % from all the values filtered on the time frame rather than the top 10 which is filtered by cases

我有一张包含 100 多个国家/地区的大表格，因此我需要显示前 10 个国家/地区，但我需要显示每个国家/地区占总数的 %market.以下是虚拟数据.

I have a big table with more than 100 countries hence I need to show top 10 countries but I need to show the %market each country has of the total. Below is the dummy data.

还要补充一点，我的数据包含过去 5 年的数据，但我正在处理过去 3/6 个月的滚动窗口.所以总和必须是过去 3/6 个月的数据.

One more thing to add, my data has the last 5 yrs of data but I'm working on the last 3/6 months rolling window. So the grand total has to be for the last 3/6 months.

这是全选时的市场份额

这是按病例过滤后排名前 10 的国家/地区

This is the top 10 countries when filtered out by cases

我需要的是，当我过滤掉前 10 个国家/地区时，我得到的市场份额是在所有记录都存在时计算的，即美国为 40% 的第二张图表.所以基本上是第二张图中的前 10 个国家，但当我只想显示前 10 个国家并重新计算时，它不应该改变值，就像它在第三张图片中所做的那样.

What I need is when I filter out on top 10 countries I get the market share which is calculated when all records are there ie 2nd graph where US is 40%. So basically top 10 countries from 2nd graph but it shouldn't change values when I want to display only top 10 countries and recalculate as it's doing in 3rd image.

推荐答案

编辑

使用您共享的表格，我创建了一个采用前 2 个国家/地区的计算方法，并计算了过去 3 个月的Total Cases.

Total Cases =
DISTINCTCOUNT ( 'Table'[Case Number] )

计算 1

计算只使用一个表.要强制每个国家/地区的过滤器上下文相同，我必须在变量 ListOfDates 上使用 CALCULAETABLE.

CaseTop10 =
VAR ListOfDates =
    CALCULATETABLE (
        DATESINPERIOD (
            'Table'[Created On],
            LASTDATE ( 'Table'[Created On] ),
            -3,
            MONTH
        ),
        ALL ( 'Table' )
    )
VAR TopCountries =
    SUMMARIZE (
        CALCULATETABLE (
            TOPN ( 2, ALL ( 'Table'[Country (Account)] ), [Total Cases], DESC ),
            ListOfDates
        ),
        [Country (Account)]
    )
VAR GTCases =
    DIVIDE (
        CALCULATE ( [Total Cases], ListOfDates ),
        CALCULATE ( CALCULATE ( [Total Cases], ListOfDates ), ALL ( 'Table' ) )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Country (Account)] ) IN TopCountries,
        GTCases,
        BLANK ()
    )

计算 2

为了简化计算最好创建一个日历表

Calculation 2

In order to simplify the calculation is better to create a Calendar Table

CaseTop10 =
VAR ListOfDates =
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
VAR TopCountries =
    SUMMARIZE (
        CALCULATETABLE (
            TOPN ( 2, ALL ( 'Table'[Country (Account)] ), [Total Cases], DESC ),
            ListOfDates
        ),
        [Country (Account)]
    )
VAR GTCases =
    DIVIDE (
        CALCULATE ( [Total Cases], ListOfDates ),
        CALCULATE ( CALCULATE ( [Total Cases], ListOfDates ), ALL ( 'Table' ) )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Country (Account)] ) IN TopCountries,
        GTCases,
        BLANK ()
    )

因此，您需要像这样创建一个日历表

So, you will need to create a Calendar table, like this

Calendar =
CALENDAR ( MIN ( 'Table'[Created On] ), MAX ( 'Table'[Created On] ) )

并建立关系.

这两个计算中的任何一个都会在右侧的表格中生成结果.

Either of those two calculations would generate the result on the table on the right.

这篇关于在 Power BI 中显示前 10 名时获取总数的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！