Forum Discussion
Measure for Share in Segment
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
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
3 Replies
v-huizhn-msftMicrosoft Employee
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,
AngeliaThanks 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