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

问题描述

我有一个表，其中有3列日期，国家和案例编号.我想展示案例最多的前十个国家.我还想显示总计的百分比.当我选择将显示值的内置选项显示为总计的％时，它将基于过滤出的前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个国家/地区，但我需要显示每个国家/地区在总市场中所占的百分比.下面是虚拟数据.

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.

这是全部选中后的市场份额

This is the market share when selected all

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

This is the top 10 countries when filtered out by cases

我需要的是，当我筛选出前10个国家/地区时，我会获得市场份额，该份额是在所有记录都存在的情况下得出的，即第二张图表，其中美国为40％.因此基本上是第二张图中的前十个国家/地区，但是当我只想显示前十个国家/地区并像在第三张图片中一样进行重新计算时，它不应更改值.

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 =
DISTINCTCOUNT ( 'Table'[Case Number] )

计算1

该计算仅使用一个表.为了使每个国家/地区的过滤器上下文都相同，我必须在变量 ListOfDates 上使用 CALCULATETABLE .

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 ()
    )

因此，您将需要创建一个Calendar表，就像这样

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