I have a question similar to my last. I am trying to figure out how to find customers who have bought only tape, pen and stapler once and nothing else.
我有一个类似于我上一个的问题。我试图弄清楚如何找到一次只购买磁带,笔和订书机的客户。
Name CustmoerID Item Name Item # Desired Results
Bob 1 Pen 555
Bob 1 Stapler 222
Bob 1 Stapler 222
Bob 1 Tape 111
Greg 2 Pen 555
Greg 2 Pen 555
Greg 2 Stapler 222
Tim 3 Stapler 222
Tim 3 Tape 666
Tim 3 Glue 333
Mark 4 Pen 555 Check
Mark 4 Stapler 222 Check
Mark 4 Tape 111 Check
This is the closet I think I got. But no luck.
这是我认为我得到的壁橱。但没有运气。
=IF(AND(OR(D2={111}),COUNTIF(B:B,B2)=1)*AND(OR(D2={222}),COUNTIF(B:B,B2)=1)*AND(OR(D2={555}),COUNTIF(B:B,B2)=1),"Check","")
Edit- This got me a bit closer but still not working properly.
编辑 - 这让我更接近但仍然无法正常工作。
=IF(AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3)*AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3)*AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3),"Check","")
3 个解决方案
#1
2
This allows the use of two codes for the same thing and checks if there are three.
这允许对同一事物使用两个代码并检查是否有三个。
=IF(AND(COUNTIF(B:B,B2)=3,SUMPRODUCT(--(COUNTIFS(B:B,B2,D:D,{222,111,777,555})=1))=3),"Check","")
#2
1
You can use the following formula to determine if a given customer has order one and only one of a give item using the following formula (in this case for Pens):
您可以使用以下公式来确定给定客户是否使用以下公式(在本例中为Pens)具有订单1和仅一个给定项目:
=COUNTIFS(B:B, B2,D:D, 555)=1
You can then translate this to staplers and tape as well. The easiest way would be to put each of these into their own column and use an AND statement, but all together it would look like:
然后,您可以将其转换为订书机和磁带。最简单的方法是将每个这些放入他们自己的列并使用AND语句,但所有这些一起看起来像:
=AND(COUNTIFS(B:B, B2,D:D, 555)=1, COUNTIFS(B:B, B2,D:D, 222)=1, COUNTIFS(B:B, B2,D:D, 111)=1)
#3
0
If you wanted the folk that had purchased any one of these items, but not 1 of each of the three items, you could use a 'Traditional' PivotTable:
如果你想要购买这些商品中的任何一件的民众,而不是三件商品中的每一件,你可以使用“传统”数据透视表:
That's just a PivotTable with both an item filter applied (using a Slicer) and a Values Filter applied, to only show results where count = 1
这只是一个数据透视表,同时应用了项目过滤器(使用切片器)和应用了值过滤器,仅显示count = 1的结果
Note that you need to check the Allow Multiple Filters Per Field option of the PivotTable Options>Totals & Filters dialog in order to set both a Filter and a Values Filter at the same time.
请注意,您需要选中“数据透视表选项”>“总计和过滤器”对话框中的“允许每个字段的多个过滤器”选项,以便同时设置“过滤器”和“值过滤器”。
If you wanted folk who had purchased exactly one of each of the three items, you could either use a traditional PivotTable with a formula running down the side (which doesn't hold any advantages to simply using a formula like Scott Craner suggests with his answer), or you could use an OLAP PivotTable and write Measures using DAX. This fairly new capability is simply awesome: You can now write complicated formulas right within a PivotTable itself.
如果你想要购买三种产品中每种产品中的一种的民众,你可以使用带有配方的传统数据透视表(这对于简单地使用像Scott Craner建议他的答案这样的公式没有任何优势),或者您可以使用OLAP数据透视表并使用DAX编写度量。这个相当新的功能非常棒:您现在可以在数据透视表本身内编写复杂的公式。
If using the PivotTable + Formula approach, it would look something like this:
如果使用数据透视表+公式方法,它看起来像这样:
...with the formula being:
......公式如下:
=AND(GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",C$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",D$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",F$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3)=3)
= AND(GETPIVOTDATA(“项目名称”,$ B $ 1,“名称”,B3,“项目#”,C $ 2)= 1,GETPIVOTDATA(“项目名称”,$ B $ 1,“名称”,B3,“项目#“,D $ 2)= 1,GETPIVOTDATA(”项目名称“,$ B $ 1,”名称“,B3,”项目#“,F $ 2)= 1,GETPIVOTDATA(”项目名称“,$ B $ 1,”名称”,B3)= 3)
But there's really no advantage between this and the great answer Scott Craner has given.
但这与斯科特克拉纳给出的伟大答案之间确实没有任何优势。
Or you can use DAX measures and an OLAP PivotTable based on the DataModel.
或者,您可以使用DAX度量和基于DataModel的OLAP数据透视表。
This requires you either to have a Excel that has the DataModel built in (i.e. Excel 2013 or later) or a version of Excel that has the PowerPivot add-in installed (Free for Excel 2010, built in to other Excel versions if you have the right premium version such as Professional Plus etc).
这要求您拥有内置DataModel的Excel(即Excel 2013或更高版本)或安装了PowerPivot加载项的Excel版本(Excel 2010免费版,内置于其他Excel版本,如果您有正确的高级版本,如Professional Plus等)。
Here's the result:
这是结果:
...and here's the measures I used: 
......这是我使用的措施:
...and here's how to add a measure if you don't have PowerPivot installed (but have Excel 2013 or later): You simply right click here:
...如果您没有安装PowerPivot(但有Excel 2013或更高版本),这里是如何添加度量:您只需右键单击此处:
This requires you to check the "Add to Data Model" option when you first make your PivotTable:
这需要您在第一次创建数据透视表时选中“添加到数据模型”选项:
UPDATE May 2018:
2018年5月更新:
I posted a question on how to do this more dynamically at https://community.powerbi.com/t5/Desktop/Identify-customers-who-had-puchased-exactly-one-unit-of-three/td-p/407941 and got 4 great answers. All four work with Excel 365, but only the third answer from Phil Seamark worked in my build of Excel 2016. Here it is:
我在https://community.powerbi.com/t5/Desktop/Identify-customers-who-had-puchased-exactly-one-unit-of-three/td-p/上发布了一个关于如何更动态地执行此操作的问题407941得到了4个很棒的答案。所有四个都使用Excel 365,但只有Phil Seamark的第三个答案适用于我的Excel 2016构建。这里是:
It works by using the old "parameter arg" trick of using 1, 2, 4, .... in a second column of Table2 so you could see if everything someone had purchased added to 7. Sneaky!
它使用在Table2的第二列中使用1,2,4 ......的旧“参数arg”技巧来工作,这样你就可以看到有人购买的所有东西都添加到了7. Sneaky!
And here's the measure:
这是衡量标准:
=COUNTROWS(
FILTER(
SUMMARIZE(
'Table1',
Table1[Customer],
"Score", SUM('Table2'[ID]),
"myRows",COUNTROWS('Table1')
),[Score]=SUM(Table2[ID]) && [myRows]=COUNTROWS(Table2))
)
#1
2
This allows the use of two codes for the same thing and checks if there are three.
这允许对同一事物使用两个代码并检查是否有三个。
=IF(AND(COUNTIF(B:B,B2)=3,SUMPRODUCT(--(COUNTIFS(B:B,B2,D:D,{222,111,777,555})=1))=3),"Check","")
#2
1
You can use the following formula to determine if a given customer has order one and only one of a give item using the following formula (in this case for Pens):
您可以使用以下公式来确定给定客户是否使用以下公式(在本例中为Pens)具有订单1和仅一个给定项目:
=COUNTIFS(B:B, B2,D:D, 555)=1
You can then translate this to staplers and tape as well. The easiest way would be to put each of these into their own column and use an AND statement, but all together it would look like:
然后,您可以将其转换为订书机和磁带。最简单的方法是将每个这些放入他们自己的列并使用AND语句,但所有这些一起看起来像:
=AND(COUNTIFS(B:B, B2,D:D, 555)=1, COUNTIFS(B:B, B2,D:D, 222)=1, COUNTIFS(B:B, B2,D:D, 111)=1)
#3
0
If you wanted the folk that had purchased any one of these items, but not 1 of each of the three items, you could use a 'Traditional' PivotTable:
如果你想要购买这些商品中的任何一件的民众,而不是三件商品中的每一件,你可以使用“传统”数据透视表:
That's just a PivotTable with both an item filter applied (using a Slicer) and a Values Filter applied, to only show results where count = 1
这只是一个数据透视表,同时应用了项目过滤器(使用切片器)和应用了值过滤器,仅显示count = 1的结果
Note that you need to check the Allow Multiple Filters Per Field option of the PivotTable Options>Totals & Filters dialog in order to set both a Filter and a Values Filter at the same time.
请注意,您需要选中“数据透视表选项”>“总计和过滤器”对话框中的“允许每个字段的多个过滤器”选项,以便同时设置“过滤器”和“值过滤器”。
If you wanted folk who had purchased exactly one of each of the three items, you could either use a traditional PivotTable with a formula running down the side (which doesn't hold any advantages to simply using a formula like Scott Craner suggests with his answer), or you could use an OLAP PivotTable and write Measures using DAX. This fairly new capability is simply awesome: You can now write complicated formulas right within a PivotTable itself.
如果你想要购买三种产品中每种产品中的一种的民众,你可以使用带有配方的传统数据透视表(这对于简单地使用像Scott Craner建议他的答案这样的公式没有任何优势),或者您可以使用OLAP数据透视表并使用DAX编写度量。这个相当新的功能非常棒:您现在可以在数据透视表本身内编写复杂的公式。
If using the PivotTable + Formula approach, it would look something like this:
如果使用数据透视表+公式方法,它看起来像这样:
...with the formula being:
......公式如下:
=AND(GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",C$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",D$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",F$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3)=3)
= AND(GETPIVOTDATA(“项目名称”,$ B $ 1,“名称”,B3,“项目#”,C $ 2)= 1,GETPIVOTDATA(“项目名称”,$ B $ 1,“名称”,B3,“项目#“,D $ 2)= 1,GETPIVOTDATA(”项目名称“,$ B $ 1,”名称“,B3,”项目#“,F $ 2)= 1,GETPIVOTDATA(”项目名称“,$ B $ 1,”名称”,B3)= 3)
But there's really no advantage between this and the great answer Scott Craner has given.
但这与斯科特克拉纳给出的伟大答案之间确实没有任何优势。
Or you can use DAX measures and an OLAP PivotTable based on the DataModel.
或者,您可以使用DAX度量和基于DataModel的OLAP数据透视表。
This requires you either to have a Excel that has the DataModel built in (i.e. Excel 2013 or later) or a version of Excel that has the PowerPivot add-in installed (Free for Excel 2010, built in to other Excel versions if you have the right premium version such as Professional Plus etc).
这要求您拥有内置DataModel的Excel(即Excel 2013或更高版本)或安装了PowerPivot加载项的Excel版本(Excel 2010免费版,内置于其他Excel版本,如果您有正确的高级版本,如Professional Plus等)。
Here's the result:
这是结果:
...and here's the measures I used:
......这是我使用的措施:
...and here's how to add a measure if you don't have PowerPivot installed (but have Excel 2013 or later): You simply right click here:
...如果您没有安装PowerPivot(但有Excel 2013或更高版本),这里是如何添加度量:您只需右键单击此处:
This requires you to check the "Add to Data Model" option when you first make your PivotTable:
这需要您在第一次创建数据透视表时选中“添加到数据模型”选项:
UPDATE May 2018:
2018年5月更新:
I posted a question on how to do this more dynamically at https://community.powerbi.com/t5/Desktop/Identify-customers-who-had-puchased-exactly-one-unit-of-three/td-p/407941 and got 4 great answers. All four work with Excel 365, but only the third answer from Phil Seamark worked in my build of Excel 2016. Here it is:
我在https://community.powerbi.com/t5/Desktop/Identify-customers-who-had-puchased-exactly-one-unit-of-three/td-p/上发布了一个关于如何更动态地执行此操作的问题407941得到了4个很棒的答案。所有四个都使用Excel 365,但只有Phil Seamark的第三个答案适用于我的Excel 2016构建。这里是:
It works by using the old "parameter arg" trick of using 1, 2, 4, .... in a second column of Table2 so you could see if everything someone had purchased added to 7. Sneaky!
它使用在Table2的第二列中使用1,2,4 ......的旧“参数arg”技巧来工作,这样你就可以看到有人购买的所有东西都添加到了7. Sneaky!
And here's the measure:
这是衡量标准:
=COUNTROWS(
FILTER(
SUMMARIZE(
'Table1',
Table1[Customer],
"Score", SUM('Table2'[ID]),
"myRows",COUNTROWS('Table1')
),[Score]=SUM(Table2[ID]) && [myRows]=COUNTROWS(Table2))
)