Measure for Share in Segment

[Anonymous]

Hi, I have 2 tables - one with sales data and one with product information and segmentation.

Salesdata					Product table
ProID	Period	Customer	Units		ProID	Segment
1001	1	101	11		1001	Car
1001	2	102	54		1002	Bike
1002	1	103	22		1003	Car
1002	3	101	12		1004	Bike
1003	4	101	15		1005	Bus

I struggle creating a new measure that will calculate the share in a segment like this:

			New Measure
ProID	Units	Total Segment	Share in Segment
1001	65	80	81 %
1002	34	34	100 %
1003	15	80	19 %

I hope you can help.

Br Kent

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Please follow the steps below and get expected result.

First, create a relation as the picture1 shown.

picture1
Second, create two calculated columns using the formula below.

Segment1 = RELATED('Product table'[Segment])

total segment = CALCULATE(SUM(Salesdata[Units]),ALLEXCEPT(Salesdata,Salesdata[Segment1]))

picture2

Third, create a new table by clicking "New Table" under Modeling on Home page using the formula below.  

Result =
SUMMARIZE (
    Salesdata,
    Salesdata[ProID],
    "Units", SUM ( Salesdata[Units] ),
    "total segment", MAX ( Salesdata[total segment] )
)

Finally, create a calculated column using the formula to get share in Segment.

Share in Segment = DIVIDE(Result[Units],Result[total segment])

picture3

Another way to get desired result using measure.

share in segment = DIVIDE(SUM(Salesdata[Units]),MAX(Salesdata[total segment]))

picture4
Please download the .pbix file for further analysis.

Best Regards,
Angelia

[Anonymous]

Thanks a lot, @v-huizhn-msft.

I was hoping that I would be able to calculate everything in one measure in a combination of CALCULATE and FILTERS.

I have found out that in the data there is a total line I would be able to use and that would make everything simpler:

Product table:
ProdID	ProductLevel	Supplier	Category	Segment
1001	Category A
1002	Segment B		A
1003	Item	Best	A	B
1004	Item	Best	A	B
1005	Item	Zoom	A	B
1006	Segment D		A
1007	Item	Best	A	D
1008	Item	Zoom	A	D
1009	Item	Cool	A	D
1010	Item	Zoom	A	D

SalesData avalible
ProdID	ProductLevel	Period	Amount
1001	Category A	1	160
1002	Segment B	1	120
1003	Item	1	30
1004	Item	1	40
1005	Item	1	50
1006	Segment D	1	40
1007	Item	1	40
1001	Category A	2	110
1002	Segment B	2	80
1003	Item	2	10
1004	Item	2	50
1005	Item	2	20
1006	Segment D	2	30
1007	Item	2	30

Decired outcome
Filter on ITEM on ProductLevel
ProdID	ProductLevel	Period	Sales	Total Segment	Share in Segment
1003	Item	1	30	120	25 %
1004	Item	1	40	120	33 %
1005	Item	1	50	120	42 %
1007	Item	1	40	40	100 %

Are you able to help me with this?

[v-huizhn-msft]

Hi @Anonymous,

How can you get Decired outcome from Product table and SalesData avalible? There is one issue in one thread. You'd better mark the first reply as answer because it gets the right outcome based on your original requirement. Then you create a new thread for your new issue. Thanks a lot.

Best Regards,
Angelia