Solved: Re: Count Distinct of segment whith sales % of fur...

Anonymous · ‎10-23-2021

- Im new to Power BI need help on below logic

Table - Orders table Superstore data set.

Sales by segment, category

% sales = DIVIDE(SUM(Orders[Sales]),CALCULATE(SUM(Orders[Sales]),ALLSELECTED(Orders[Category])))

Furniture% = DIVIDE( CALCULATE(SUM(Orders[Sales]),Orders[Category]="Furniture"),CALCULATE(SUM(Orders[Sales]),allselected(Orders[Category])))

Now how do I calculate distinct count of segment with furniture % greater than 30% ? which is Two.

Please also let me know if the existing calculations can be done in a better way.

Thanks in advance.

v-rzhou-msft · ‎10-25-2021

Hi @Anonymous

Try this code.

DistinctCount = 
VAR _T = SUMMARIZE(Orders,Orders[Segment],"Furniture%",[Furniture%])
RETURN
COUNTAX(FILTER(_T,[Furniture%]> 0.3),[Segment])

I use this code to have a test in your sample. Due to [Furniture%] in your sample is not the same as your screenshot. Here I let [Furniture%]>0.2 in my code. Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous · ‎10-23-2021

This is a small subset of the data.

I want to get the distinct count of segment with % greater than a value for one category.

Please refer to the screenshots in the above post.

Segment	Category	Sales
Corporate	Technology	59.97
Corporate	Office Supplies	78.304
Corporate	Office Supplies	21.456
Corporate	Furniture	207.846
Home Office	Technology	2999.95
Home Office	Office Supplies	51.45
Home Office	Office Supplies	11.96
Home Office	Office Supplies	1126.02
Corporate	Furniture	210.98
Home Office	Office Supplies	242.94
Home Office	Technology	179.97
Home Office	Office Supplies	99.696
Home Office	Office Supplies	27.936
Home Office	Furniture	84.98
Home Office	Office Supplies	18.72
Corporate	Office Supplies	18.28
Corporate	Technology	207
Corporate	Office Supplies	32.35
Corporate	Office Supplies	7.71
Corporate	Office Supplies	40.3
Corporate	Furniture	34.58
Corporate	Furniture	22.23
Corporate	Technology	215.968
Consumer	Furniture	48.896
Corporate	Technology	3059.982
Home Office	Office Supplies	348.208
Home Office	Office Supplies	35.784
Consumer	Office Supplies	21.744
Consumer	Technology	7.92
Home Office	Office Supplies	81.92
Home Office	Technology	889.536
Home Office	Furniture	892.224
Home Office	Office Supplies	223.92
Home Office	Office Supplies	23.12
Consumer	Office Supplies	5.229
Consumer	Office Supplies	285.552
Corporate	Technology	695.7
Corporate	Office Supplies	15.66
Corporate	Office Supplies	28.854
Home Office	Technology	239.97
Home Office	Furniture	37.74
Corporate	Office Supplies	21.336
Home Office	Furniture	203.983
Consumer	Technology	160.776
Corporate	Office Supplies	1245.86
Home Office	Office Supplies	40.08
Home Office	Furniture	37.68
Consumer	Office Supplies	6
Consumer	Office Supplies	4.956
Consumer	Office Supplies	37.44
Consumer	Office Supplies	2.808
Consumer	Office Supplies	14.016
Consumer	Technology	71.976
Consumer	Technology	107.982
Consumer	Furniture	359.97
Corporate	Furniture	892.98
Consumer	Office Supplies	23.12
Consumer	Office Supplies	5.67
Consumer	Office Supplies	24.2
Consumer	Technology	359.976
Home Office	Office Supplies	14.4
Home Office	Technology	619.95
Home Office	Office Supplies	89.52
Home Office	Technology	350.973
Home Office	Technology	164.99
Consumer	Office Supplies	6.63
Consumer	Technology	799.96
Consumer	Furniture	107.53
Consumer	Office Supplies	34.384
Consumer	Office Supplies	1924.16
Corporate	Office Supplies	15.47
Corporate	Office Supplies	7.16
Consumer	Office Supplies	11.22
Consumer	Office Supplies	129.3
Consumer	Office Supplies	252.784
Consumer	Technology	127.984
Consumer	Furniture	3.984
Consumer	Office Supplies	12.992
Corporate	Furniture	963.136
Corporate	Technology	88.776
Home Office	Office Supplies	6.56
Home Office	Office Supplies	13.11
Consumer	Office Supplies	1.788
Consumer	Office Supplies	37.94
Home Office	Furniture	480.96
Home Office	Technology	124.792
Consumer	Office Supplies	12.128
Consumer	Office Supplies	12.7
Home Office	Office Supplies	12.84
Home Office	Furniture	44.67
Consumer	Furniture	474.43
Home Office	Office Supplies	12.74
Home Office	Office Supplies	8.82
Home Office	Furniture	120.784
Corporate	Technology	169.064
Corporate	Office Supplies	168.624
Consumer	Technology	29.16
Corporate	Office Supplies	9.432
Home Office	Office Supplies	11.76
Home Office	Office Supplies	166.45
Consumer	Office Supplies	3.882
Consumer	Office Supplies	115.296
Consumer	Office Supplies	279.9
Consumer	Office Supplies	18.336
Consumer	Office Supplies	36.288
Consumer	Technology	111.984
Consumer	Office Supplies	5.97
Consumer	Office Supplies	2.508
Home Office	Office Supplies	33.74
Consumer	Office Supplies	8.34
Consumer	Office Supplies	8.57
Consumer	Office Supplies	119.616
Home Office	Office Supplies	21.4
Consumer	Furniture	86.26
Consumer	Office Supplies	139.04
Consumer	Office Supplies	46.8
Consumer	Office Supplies	38.88
Corporate	Furniture	27.92
Corporate	Furniture	399.672
Corporate	Technology	167.976
Corporate	Technology	109.53
Corporate	Office Supplies	9.82
Consumer	Office Supplies	3.52
Consumer	Furniture	419.136
Consumer	Office Supplies	18.16
Consumer	Office Supplies	25.02
Consumer	Office Supplies	10.71
Corporate	Office Supplies	9.78
Corporate	Furniture	21.12
Consumer	Furniture	240.784
Corporate	Office Supplies	8.4
Corporate	Technology	71.96
Corporate	Office Supplies	579.51
Corporate	Technology	14.99
Corporate	Office Supplies	19.68
Corporate	Office Supplies	25.92
Corporate	Office Supplies	6.48
Corporate	Technology	86.352
Consumer	Office Supplies	32.784
Consumer	Technology	47.984
Consumer	Technology	62.592
Consumer	Office Supplies	4.276
Consumer	Office Supplies	5443.96
Consumer	Furniture	302.67
Consumer	Office Supplies	56.07
Consumer	Office Supplies	243.992
Consumer	Office Supplies	7.12
Corporate	Office Supplies	32.4
Corporate	Office Supplies	209.94
Corporate	Office Supplies	4164.05
Corporate	Furniture	212.94
Home Office	Technology	333.576
Home Office	Technology	31.992
Home Office	Office Supplies	51.168
Home Office	Office Supplies	10.64
Home Office	Furniture	68.704
Home Office	Furniture	386.91
Home Office	Office Supplies	3.6
Home Office	Technology	449.91
Corporate	Office Supplies	1640.7
Corporate	Technology	371.2
Consumer	Office Supplies	356.79
Consumer	Office Supplies	154.9
Corporate	Technology	227.46
Corporate	Office Supplies	46.24
Consumer	Office Supplies	5.22
Consumer	Office Supplies	32.67
Corporate	Office Supplies	17.94
Consumer	Office Supplies	274.491
Consumer	Technology	137.94
Corporate	Office Supplies	123.92
Corporate	Technology	1319.8
Consumer	Office Supplies	354.9
Home Office	Office Supplies	29.9
Consumer	Office Supplies	153.78
Consumer	Office Supplies	61.02
Consumer	Office Supplies	110.11
Consumer	Office Supplies	7.89
Consumer	Office Supplies	11.52
Home Office	Office Supplies	5.184
Home Office	Office Supplies	65.584
Home Office	Furniture	22.2
Home Office	Office Supplies	419.4
Consumer	Furniture	196.784
Consumer	Furniture	231.92
Consumer	Office Supplies	11.84
Corporate	Office Supplies	15.12
Corporate	Office Supplies	7.872
Consumer	Office Supplies	454.56
Consumer	Furniture	141.42
Consumer	Furniture	310.744
Consumer	Office Supplies	12.736
Consumer	Office Supplies	6.47
Consumer	Office Supplies	13.748
Consumer	Office Supplies	15.224
Home Office	Furniture	89.0664
Home Office	Office Supplies	175.44
Home Office	Technology	438.336
Consumer	Furniture	69.375
Consumer	Office Supplies	31.68
Consumer	Technology	2003.168
Consumer	Office Supplies	9.344
Consumer	Furniture	62.72
Consumer	Technology	2939.93
Consumer	Office Supplies	31.744
Consumer	Office Supplies	5.432
Consumer	Furniture	913.43
Consumer	Office Supplies	372.144
Home Office	Technology	604.768
Corporate	Office Supplies	20.7
Consumer	Office Supplies	4.768
Corporate	Office Supplies	760.98
Consumer	Office Supplies	18.368
Consumer	Furniture	600.558
Consumer	Office Supplies	50.352
Consumer	Office Supplies	28.032
Consumer	Furniture	7.692
Corporate	Furniture	210.58
Corporate	Office Supplies	30.96
Corporate	Technology	239.984
Consumer	Technology	429.6
Consumer	Furniture	31.968
Consumer	Furniture	887.271
Consumer	Office Supplies	21.696
Consumer	Furniture	147.568
Consumer	Technology	47.904
Home Office	Furniture	455.97
Home Office	Office Supplies	5.715
Home Office	Technology	57.594
Home Office	Furniture	30.144
Home Office	Furniture	899.43
Consumer	Office Supplies	79.92
Consumer	Office Supplies	12.28
Consumer	Office Supplies	542.94
Consumer	Office Supplies	2.04
Consumer	Office Supplies	2022.272
Consumer	Office Supplies	9.12
Corporate	Office Supplies	25.86
Corporate	Office Supplies	276.784
Corporate	Technology	110.352
Consumer	Office Supplies	90.64
Home Office	Technology	95.984
Home Office	Office Supplies	4.938
Corporate	Technology	16.59
Consumer	Office Supplies	268.576
Consumer	Office Supplies	9.84
Consumer	Office Supplies	7.78
Home Office	Office Supplies	67.4
Home Office	Office Supplies	2.52
Home Office	Technology	67.8
Home Office	Office Supplies	18.76
Home Office	Technology	12.12
Home Office	Office Supplies	11.34
Home Office	Technology	159.8
Home Office	Furniture	18.96
Consumer	Office Supplies	12.96
Consumer	Office Supplies	94.85
Consumer	Office Supplies	13.48
Consumer	Furniture	14.91
Consumer	Furniture	91.96
Consumer	Technology	258.576
Consumer	Office Supplies	29.6

Thank you.

vanessafvg · ‎10-23-2021

it is much easier if you provide the data in text format so the person assisting can use this to test the solution they are giving you.

Please provide the data in text format.

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Anonymous · ‎10-24-2021