cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sivaram310
Regular Visitor

Count Distinct of segment whith sales % of furniture greater than 30%

- Im new to Power BI need help on below logic

Table - Orders table Superstore data set.

 

Sales by segment, category

Sivaram310_0-1635022164893.png

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

Sivaram310_2-1635022229374.png

Furniture% = DIVIDE( CALCULATE(SUM(Orders[Sales]),Orders[Category]="Furniture"),CALCULATE(SUM(Orders[Sales]),allselected(Orders[Category])))
Sivaram310_3-1635022320882.png
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.
 

 

1 ACCEPTED SOLUTION

Hi @Sivaram310 

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.

1.png

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

5 REPLIES 5
Sivaram310
Regular Visitor

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.

SegmentCategorySales
CorporateTechnology59.97
CorporateOffice Supplies78.304
CorporateOffice Supplies21.456
CorporateFurniture207.846
Home OfficeTechnology2999.95
Home OfficeOffice Supplies51.45
Home OfficeOffice Supplies11.96
Home OfficeOffice Supplies1126.02
CorporateFurniture210.98
Home OfficeOffice Supplies242.94
Home OfficeTechnology179.97
Home OfficeOffice Supplies99.696
Home OfficeOffice Supplies27.936
Home OfficeFurniture84.98
Home OfficeOffice Supplies18.72
CorporateOffice Supplies18.28
CorporateTechnology207
CorporateOffice Supplies32.35
CorporateOffice Supplies7.71
CorporateOffice Supplies40.3
CorporateFurniture34.58
CorporateFurniture22.23
CorporateTechnology215.968
ConsumerFurniture48.896
CorporateTechnology3059.982
Home OfficeOffice Supplies348.208
Home OfficeOffice Supplies35.784
ConsumerOffice Supplies21.744
ConsumerTechnology7.92
Home OfficeOffice Supplies81.92
Home OfficeTechnology889.536
Home OfficeFurniture892.224
Home OfficeOffice Supplies223.92
Home OfficeOffice Supplies23.12
ConsumerOffice Supplies5.229
ConsumerOffice Supplies285.552
CorporateTechnology695.7
CorporateOffice Supplies15.66
CorporateOffice Supplies28.854
Home OfficeTechnology239.97
Home OfficeFurniture37.74
CorporateOffice Supplies21.336
Home OfficeFurniture203.983
ConsumerTechnology160.776
CorporateOffice Supplies1245.86
Home OfficeOffice Supplies40.08
Home OfficeFurniture37.68
ConsumerOffice Supplies6
ConsumerOffice Supplies4.956
ConsumerOffice Supplies37.44
ConsumerOffice Supplies2.808
ConsumerOffice Supplies14.016
ConsumerTechnology71.976
ConsumerTechnology107.982
ConsumerFurniture359.97
CorporateFurniture892.98
ConsumerOffice Supplies23.12
ConsumerOffice Supplies5.67
ConsumerOffice Supplies24.2
ConsumerTechnology359.976
Home OfficeOffice Supplies14.4
Home OfficeTechnology619.95
Home OfficeOffice Supplies89.52
Home OfficeTechnology350.973
Home OfficeTechnology164.99
ConsumerOffice Supplies6.63
ConsumerTechnology799.96
ConsumerFurniture107.53
ConsumerOffice Supplies34.384
ConsumerOffice Supplies1924.16
CorporateOffice Supplies15.47
CorporateOffice Supplies7.16
ConsumerOffice Supplies11.22
ConsumerOffice Supplies129.3
ConsumerOffice Supplies252.784
ConsumerTechnology127.984
ConsumerFurniture3.984
ConsumerOffice Supplies12.992
CorporateFurniture963.136
CorporateTechnology88.776
Home OfficeOffice Supplies6.56
Home OfficeOffice Supplies13.11
ConsumerOffice Supplies1.788
ConsumerOffice Supplies37.94
Home OfficeFurniture480.96
Home OfficeTechnology124.792
ConsumerOffice Supplies12.128
ConsumerOffice Supplies12.7
Home OfficeOffice Supplies12.84
Home OfficeFurniture44.67
ConsumerFurniture474.43
Home OfficeOffice Supplies12.74
Home OfficeOffice Supplies8.82
Home OfficeFurniture120.784
CorporateTechnology169.064
CorporateOffice Supplies168.624
ConsumerTechnology29.16
CorporateOffice Supplies9.432
Home OfficeOffice Supplies11.76
Home OfficeOffice Supplies166.45
ConsumerOffice Supplies3.882
ConsumerOffice Supplies115.296
ConsumerOffice Supplies279.9
ConsumerOffice Supplies18.336
ConsumerOffice Supplies36.288
ConsumerTechnology111.984
ConsumerOffice Supplies5.97
ConsumerOffice Supplies2.508
Home OfficeOffice Supplies33.74
ConsumerOffice Supplies8.34
ConsumerOffice Supplies8.57
ConsumerOffice Supplies119.616
Home OfficeOffice Supplies21.4
ConsumerFurniture86.26
ConsumerOffice Supplies139.04
ConsumerOffice Supplies46.8
ConsumerOffice Supplies38.88
CorporateFurniture27.92
CorporateFurniture399.672
CorporateTechnology167.976
CorporateTechnology109.53
CorporateOffice Supplies9.82
ConsumerOffice Supplies3.52
ConsumerFurniture419.136
ConsumerOffice Supplies18.16
ConsumerOffice Supplies25.02
ConsumerOffice Supplies10.71
CorporateOffice Supplies9.78
CorporateFurniture21.12
ConsumerFurniture240.784
CorporateOffice Supplies8.4
CorporateTechnology71.96
CorporateOffice Supplies579.51
CorporateTechnology14.99
CorporateOffice Supplies19.68
CorporateOffice Supplies25.92
CorporateOffice Supplies6.48
CorporateTechnology86.352
ConsumerOffice Supplies32.784
ConsumerTechnology47.984
ConsumerTechnology62.592
ConsumerOffice Supplies4.276
ConsumerOffice Supplies5443.96
ConsumerFurniture302.67
ConsumerOffice Supplies56.07
ConsumerOffice Supplies243.992
ConsumerOffice Supplies7.12
CorporateOffice Supplies32.4
CorporateOffice Supplies209.94
CorporateOffice Supplies4164.05
CorporateFurniture212.94
Home OfficeTechnology333.576
Home OfficeTechnology31.992
Home OfficeOffice Supplies51.168
Home OfficeOffice Supplies10.64
Home OfficeFurniture68.704
Home OfficeFurniture386.91
Home OfficeOffice Supplies3.6
Home OfficeTechnology449.91
CorporateOffice Supplies1640.7
CorporateTechnology371.2
ConsumerOffice Supplies356.79
ConsumerOffice Supplies154.9
CorporateTechnology227.46
CorporateOffice Supplies46.24
ConsumerOffice Supplies5.22
ConsumerOffice Supplies32.67
CorporateOffice Supplies17.94
ConsumerOffice Supplies274.491
ConsumerTechnology137.94
CorporateOffice Supplies123.92
CorporateTechnology1319.8
ConsumerOffice Supplies354.9
Home OfficeOffice Supplies29.9
ConsumerOffice Supplies153.78
ConsumerOffice Supplies61.02
ConsumerOffice Supplies110.11
ConsumerOffice Supplies7.89
ConsumerOffice Supplies11.52
Home OfficeOffice Supplies5.184
Home OfficeOffice Supplies65.584
Home OfficeFurniture22.2
Home OfficeOffice Supplies419.4
ConsumerFurniture196.784
ConsumerFurniture231.92
ConsumerOffice Supplies11.84
CorporateOffice Supplies15.12
CorporateOffice Supplies7.872
ConsumerOffice Supplies454.56
ConsumerFurniture141.42
ConsumerFurniture310.744
ConsumerOffice Supplies12.736
ConsumerOffice Supplies6.47
ConsumerOffice Supplies13.748
ConsumerOffice Supplies15.224
Home OfficeFurniture89.0664
Home OfficeOffice Supplies175.44
Home OfficeTechnology438.336
ConsumerFurniture69.375
ConsumerOffice Supplies31.68
ConsumerTechnology2003.168
ConsumerOffice Supplies9.344
ConsumerFurniture62.72
ConsumerTechnology2939.93
ConsumerOffice Supplies31.744
ConsumerOffice Supplies5.432
ConsumerFurniture913.43
ConsumerOffice Supplies372.144
Home OfficeTechnology604.768
CorporateOffice Supplies20.7
ConsumerOffice Supplies4.768
CorporateOffice Supplies760.98
ConsumerOffice Supplies18.368
ConsumerFurniture600.558
ConsumerOffice Supplies50.352
ConsumerOffice Supplies28.032
ConsumerFurniture7.692
CorporateFurniture210.58
CorporateOffice Supplies30.96
CorporateTechnology239.984
ConsumerTechnology429.6
ConsumerFurniture31.968
ConsumerFurniture887.271
ConsumerOffice Supplies21.696
ConsumerFurniture147.568
ConsumerTechnology47.904
Home OfficeFurniture455.97
Home OfficeOffice Supplies5.715
Home OfficeTechnology57.594
Home OfficeFurniture30.144
Home OfficeFurniture899.43
ConsumerOffice Supplies79.92
ConsumerOffice Supplies12.28
ConsumerOffice Supplies542.94
ConsumerOffice Supplies2.04
ConsumerOffice Supplies2022.272
ConsumerOffice Supplies9.12
CorporateOffice Supplies25.86
CorporateOffice Supplies276.784
CorporateTechnology110.352
ConsumerOffice Supplies90.64
Home OfficeTechnology95.984
Home OfficeOffice Supplies4.938
CorporateTechnology16.59
ConsumerOffice Supplies268.576
ConsumerOffice Supplies9.84
ConsumerOffice Supplies7.78
Home OfficeOffice Supplies67.4
Home OfficeOffice Supplies2.52
Home OfficeTechnology67.8
Home OfficeOffice Supplies18.76
Home OfficeTechnology12.12
Home OfficeOffice Supplies11.34
Home OfficeTechnology159.8
Home OfficeFurniture18.96
ConsumerOffice Supplies12.96
ConsumerOffice Supplies94.85
ConsumerOffice Supplies13.48
ConsumerFurniture14.91
ConsumerFurniture91.96
ConsumerTechnology258.576
ConsumerOffice Supplies29.6

Thank you.

vanessafvg
Super User
Super User

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!




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.

SegmentCategorySales
CorporateTechnology59.97
CorporateOffice Supplies78.304
CorporateOffice Supplies21.456
CorporateFurniture207.846
Home OfficeTechnology2999.95
Home OfficeOffice Supplies51.45
Home OfficeOffice Supplies11.96
Home OfficeOffice Supplies1126.02
CorporateFurniture210.98
Home OfficeOffice Supplies242.94
Home OfficeTechnology179.97
Home OfficeOffice Supplies99.696
Home OfficeOffice Supplies27.936
Home OfficeFurniture84.98
Home OfficeOffice Supplies18.72
CorporateOffice Supplies18.28
CorporateTechnology207
CorporateOffice Supplies32.35
CorporateOffice Supplies7.71
CorporateOffice Supplies40.3
CorporateFurniture34.58
CorporateFurniture22.23
CorporateTechnology215.968
ConsumerFurniture48.896
CorporateTechnology3059.982
Home OfficeOffice Supplies348.208
Home OfficeOffice Supplies35.784
ConsumerOffice Supplies21.744
ConsumerTechnology7.92
Home OfficeOffice Supplies81.92
Home OfficeTechnology889.536
Home OfficeFurniture892.224
Home OfficeOffice Supplies223.92
Home OfficeOffice Supplies23.12
ConsumerOffice Supplies5.229
ConsumerOffice Supplies285.552
CorporateTechnology695.7
CorporateOffice Supplies15.66
CorporateOffice Supplies28.854
Home OfficeTechnology239.97
Home OfficeFurniture37.74
CorporateOffice Supplies21.336
Home OfficeFurniture203.983
ConsumerTechnology160.776
CorporateOffice Supplies1245.86
Home OfficeOffice Supplies40.08
Home OfficeFurniture37.68
ConsumerOffice Supplies6
ConsumerOffice Supplies4.956
ConsumerOffice Supplies37.44
ConsumerOffice Supplies2.808
ConsumerOffice Supplies14.016
ConsumerTechnology71.976
ConsumerTechnology107.982
ConsumerFurniture359.97
CorporateFurniture892.98
ConsumerOffice Supplies23.12
ConsumerOffice Supplies5.67
ConsumerOffice Supplies24.2
ConsumerTechnology359.976
Home OfficeOffice Supplies14.4
Home OfficeTechnology619.95
Home OfficeOffice Supplies89.52
Home OfficeTechnology350.973
Home OfficeTechnology164.99
ConsumerOffice Supplies6.63
ConsumerTechnology799.96
ConsumerFurniture107.53
ConsumerOffice Supplies34.384
ConsumerOffice Supplies1924.16
CorporateOffice Supplies15.47
CorporateOffice Supplies7.16
ConsumerOffice Supplies11.22
ConsumerOffice Supplies129.3
ConsumerOffice Supplies252.784
ConsumerTechnology127.984
ConsumerFurniture3.984
ConsumerOffice Supplies12.992
CorporateFurniture963.136
CorporateTechnology88.776
Home OfficeOffice Supplies6.56
Home OfficeOffice Supplies13.11
ConsumerOffice Supplies1.788
ConsumerOffice Supplies37.94
Home OfficeFurniture480.96
Home OfficeTechnology124.792
ConsumerOffice Supplies12.128
ConsumerOffice Supplies12.7
Home OfficeOffice Supplies12.84
Home OfficeFurniture44.67
ConsumerFurniture474.43
Home OfficeOffice Supplies12.74
Home OfficeOffice Supplies8.82
Home OfficeFurniture120.784
CorporateTechnology169.064
CorporateOffice Supplies168.624
ConsumerTechnology29.16
CorporateOffice Supplies9.432
Home OfficeOffice Supplies11.76
Home OfficeOffice Supplies166.45
ConsumerOffice Supplies3.882
ConsumerOffice Supplies115.296
ConsumerOffice Supplies279.9
ConsumerOffice Supplies18.336
ConsumerOffice Supplies36.288
ConsumerTechnology111.984
ConsumerOffice Supplies5.97
ConsumerOffice Supplies2.508
Home OfficeOffice Supplies33.74
ConsumerOffice Supplies8.34
ConsumerOffice Supplies8.57
ConsumerOffice Supplies119.616
Home OfficeOffice Supplies21.4
ConsumerFurniture86.26
ConsumerOffice Supplies139.04
ConsumerOffice Supplies46.8
ConsumerOffice Supplies38.88
CorporateFurniture27.92
CorporateFurniture399.672
CorporateTechnology167.976
CorporateTechnology109.53
CorporateOffice Supplies9.82
ConsumerOffice Supplies3.52
ConsumerFurniture419.136
ConsumerOffice Supplies18.16
ConsumerOffice Supplies25.02
ConsumerOffice Supplies10.71
CorporateOffice Supplies9.78
CorporateFurniture21.12
ConsumerFurniture240.784
CorporateOffice Supplies8.4
CorporateTechnology71.96
CorporateOffice Supplies579.51
CorporateTechnology14.99
CorporateOffice Supplies19.68
CorporateOffice Supplies25.92
CorporateOffice Supplies6.48
CorporateTechnology86.352
ConsumerOffice Supplies32.784
ConsumerTechnology47.984
ConsumerTechnology62.592
ConsumerOffice Supplies4.276
ConsumerOffice Supplies5443.96
ConsumerFurniture302.67
ConsumerOffice Supplies56.07
ConsumerOffice Supplies243.992
ConsumerOffice Supplies7.12
CorporateOffice Supplies32.4
CorporateOffice Supplies209.94
CorporateOffice Supplies4164.05
CorporateFurniture212.94
Home OfficeTechnology333.576
Home OfficeTechnology31.992
Home OfficeOffice Supplies51.168
Home OfficeOffice Supplies10.64
Home OfficeFurniture68.704
Home OfficeFurniture386.91
Home OfficeOffice Supplies3.6
Home OfficeTechnology449.91
CorporateOffice Supplies1640.7
CorporateTechnology371.2
ConsumerOffice Supplies356.79
ConsumerOffice Supplies154.9
CorporateTechnology227.46
CorporateOffice Supplies46.24
ConsumerOffice Supplies5.22
ConsumerOffice Supplies32.67
CorporateOffice Supplies17.94
ConsumerOffice Supplies274.491
ConsumerTechnology137.94
CorporateOffice Supplies123.92
CorporateTechnology1319.8
ConsumerOffice Supplies354.9
Home OfficeOffice Supplies29.9
ConsumerOffice Supplies153.78
ConsumerOffice Supplies61.02
ConsumerOffice Supplies110.11
ConsumerOffice Supplies7.89
ConsumerOffice Supplies11.52
Home OfficeOffice Supplies5.184
Home OfficeOffice Supplies65.584
Home OfficeFurniture22.2
Home OfficeOffice Supplies419.4
ConsumerFurniture196.784
ConsumerFurniture231.92
ConsumerOffice Supplies11.84
CorporateOffice Supplies15.12
CorporateOffice Supplies7.872
ConsumerOffice Supplies454.56
ConsumerFurniture141.42
ConsumerFurniture310.744
ConsumerOffice Supplies12.736
ConsumerOffice Supplies6.47
ConsumerOffice Supplies13.748
ConsumerOffice Supplies15.224
Home OfficeFurniture89.0664
Home OfficeOffice Supplies175.44
Home OfficeTechnology438.336
ConsumerFurniture69.375
ConsumerOffice Supplies31.68
ConsumerTechnology2003.168
ConsumerOffice Supplies9.344
ConsumerFurniture62.72
ConsumerTechnology2939.93
ConsumerOffice Supplies31.744
ConsumerOffice Supplies5.432
ConsumerFurniture913.43
ConsumerOffice Supplies372.144
Home OfficeTechnology604.768
CorporateOffice Supplies20.7
ConsumerOffice Supplies4.768
CorporateOffice Supplies760.98
ConsumerOffice Supplies18.368
ConsumerFurniture600.558
ConsumerOffice Supplies50.352
ConsumerOffice Supplies28.032
ConsumerFurniture7.692
CorporateFurniture210.58
CorporateOffice Supplies30.96
CorporateTechnology239.984
ConsumerTechnology429.6
ConsumerFurniture31.968
ConsumerFurniture887.271
ConsumerOffice Supplies21.696
ConsumerFurniture147.568
ConsumerTechnology47.904
Home OfficeFurniture455.97
Home OfficeOffice Supplies5.715
Home OfficeTechnology57.594
Home OfficeFurniture30.144
Home OfficeFurniture899.43
ConsumerOffice Supplies79.92
ConsumerOffice Supplies12.28
ConsumerOffice Supplies542.94
ConsumerOffice Supplies2.04
ConsumerOffice Supplies2022.272
ConsumerOffice Supplies9.12
CorporateOffice Supplies25.86
CorporateOffice Supplies276.784
CorporateTechnology110.352
ConsumerOffice Supplies90.64
Home OfficeTechnology95.984
Home OfficeOffice Supplies4.938
CorporateTechnology16.59
ConsumerOffice Supplies268.576
ConsumerOffice Supplies9.84
ConsumerOffice Supplies7.78
Home OfficeOffice Supplies67.4
Home OfficeOffice Supplies2.52
Home OfficeTechnology67.8
Home OfficeOffice Supplies18.76
Home OfficeTechnology12.12
Home OfficeOffice Supplies11.34
Home OfficeTechnology159.8
Home OfficeFurniture18.96
ConsumerOffice Supplies12.96
ConsumerOffice Supplies94.85
ConsumerOffice Supplies13.48
ConsumerFurniture14.91
ConsumerFurniture91.96
ConsumerTechnology258.576
ConsumerOffice Supplies29.6

Thank you.

Hi @Sivaram310 

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.

1.png

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.

Hi @RicoZhou 

It is working

Thank you so much for helping me out.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors