Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 100...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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

     

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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:    
ProdIDProductLevelSupplierCategorySegment
1001Category A   
1002Segment B A 
1003ItemBestAB
1004ItemBestAB
1005ItemZoomAB
1006Segment D A 
1007ItemBestAD
1008ItemZoomAD
1009ItemCoolAD
1010ItemZoomAD

 

SalesData avalible  
ProdIDProductLevelPeriodAmount
1001Category A1160
1002Segment B1120
1003Item130
1004Item140
1005Item150
1006Segment D140
1007Item140
1001Category A2110
1002Segment B280
1003Item210
1004Item250
1005Item220
1006Segment D230
1007Item230

 

Decired outcome    
Filter on ITEM on ProductLevel   
ProdIDProductLevelPeriodSalesTotal SegmentShare in Segment
1003Item13012025 %
1004Item14012033 %
1005Item15012042 %
1007Item14040100 %

 

Are you able to help me with this?

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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