Power BI - 矩阵计数空白行 [英] Power BI - Matrix Count Blank Rows
问题描述
我创建了一个矩阵,其中包含访问过的销售人员和他们的化学家的数据.
我添加了一个计数公式
客户计数 = DISTINCTCOUNTNOBLANK('源数据'[CustomerNameFull])
上面矩阵中的总数是拜访过化学家的推销员的总数,我想计算没有拜访过化学家的化学家总数.即空白的计数.空白计数应与总值一起显示.
源文件:
任何帮助将不胜感激.
有了预期的结果,我提出以下措施.为选定的推销员计算不同的未访问化学家:
未到访的化学家 :=VAR _customerCount = CALCULATE(DISTINCTCOUNTNOBLANK('源数据'[CustomerNameFull]),ALLSELECTED(日期))VAR _visitedCount = [客户数量]返回 _customerCount - _visitedCount
还有一个格式化矩阵:
未访问的客户数 =IF ( [客户数量] <> 空白 () ,转变 (真的 (),NOT IINSCOPE ('源数据'[CustomerNameFull]) &&IINSCOPE ( 日期[月] ) ,[客户数] &"(& [未访问的化学家] & ")",格式([客户数量],#")))
请注意,这里我使用的是日期表,而不是在 INVDATE
列上使用自动日期/时间.无论如何,这被认为是最佳实践.请记住设置正确的排序列,请参阅此处的指南:
结果如下:
I have created a matrix having data of salesman and their chemist visited.
I have added a count formula
Customer Count = DISTINCTCOUNTNOBLANK('Source Data'[CustomerNameFull])
The total in the above matrix is of the salesman who visited the chemist, along with that I want to calculate the total of chemist that is not visited by a salesman. i.e. the count of the blank. The blank count should be shown along with the total value.
Source File: BI SAS
Expected Output
Below is the expected output that I want. The zero total is the distinct count
Any help would be highly appreciated.
With the expected result in place I propose the following measures. One to count the distinct unvisited chemists for the selected salesman/salesmen:
Unvisited Chemists :=
VAR _customerCount = CALCULATE ( DISTINCTCOUNTNOBLANK ( 'Source Data'[CustomerNameFull] ) , ALLSELECTED ( Dates ) )
VAR _visitedCount = [Customer Count]
RETURN _customerCount - _visitedCount
And one to format the matrix:
Customer Count w/ Unvisited =
IF ( [Customer Count] <> BLANK () ,
SWITCH (
TRUE (),
NOT ISINSCOPE ( 'Source Data'[CustomerNameFull] ) && ISINSCOPE ( Dates[Month] ) ,
[Customer Count] & " (" & [Unvisited Chemists] & ")" ,
FORMAT ( [Customer Count] , "#" )
)
)
Please note that here I am using a date table instead of using auto date/time on the INVDATE
column. This is considered best practice anyway. Remember to set the correct sort column, see guide here: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column
I have used a simple date table:
Dates =
ADDCOLUMNS (
CALENDARAUTO () ,
"Year" , YEAR ( [Date] ) ,
"MonthNo" , MONTH ( [Date] ) ,
"Month" , FORMAT ( [Date] , "MMMM" )
)
The data model looks like this:
The result looks like this:
这篇关于Power BI - 矩阵计数空白行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!