Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to calculate item group point? (difficult)

Hello everyone. 

 

I am under great pressure. My Boss required me to do this calculation within 2 days. I am really stuck. This is so important to me. Please help me. 

Edit: Pardon me on the title. I did search about this from internet but couldn't find anything. I did not now how to write the title

 

What I am trying to do is: 

I work in a distributing company. I am trying to calculate SalesRep auditing score. We have auditors who checks SalesRep performance in every outlet. What auditors do is they visit outlets and check if our products are present at their shelves. They check every single SKU and if they are present they input true (1) if not it will be false (0). So we calculate SalesRep performance based on their SUM of True values. But on some subchannels we group up some items. for example we group up 2 SKUs together. so calculation will be if either of the SKUs are present they get 1 full score (if both SKUs are present they still get 1). and in some product types we group 4 SKUs. if any 2 of the SKUs are present SalesReps get 2 full scores. if all 4 SKUs are present they still get 2 score.

here is the data:
 ItemGrouping table: 

TypeSKUChannelSKUGroupType-Subchannel-SKUGroup
ItemType1Item01Channel A1ItemType1-Channel A-1
ItemType1Item02Channel A2ItemType1-Channel A-2
ItemType1Item03Channel A3ItemType1-Channel A-3
ItemType1Item04Channel A3ItemType1-Channel A-3
ItemType1Item05Channel A4ItemType1-Channel A-4
ItemType2Item10Channel A1ItemType2-Channel A-1
ItemType2Item11Channel A2ItemType2-Channel A-2
ItemType2Item12Channel A2ItemType2-Channel A-2
ItemType2Item13Channel A2ItemType2-Channel A-2
ItemType2Item14Channel A2ItemType2-Channel A-2
ItemType1Item01Channel B1ItemType1-Channel B-1
ItemType1Item02Channel B1ItemType1-Channel B-1
ItemType1Item03Channel B2ItemType1-Channel B-2
ItemType1Item04Channel B3ItemType1-Channel B-3
ItemType1Item05Channel B4ItemType1-Channel B-4
ItemType2Item10Channel B1ItemType2-Channel B-1
ItemType2Item11Channel B1ItemType2-Channel B-1
ItemType2Item12Channel B1ItemType2-Channel B-1
ItemType2Item13Channel B1ItemType2-Channel B-1
ItemType2Item14Channel B2ItemType2-Channel B-2

Type-Subchannel-SKUGroup column is just concatenating key columns. 

in the table above In Channel A. ItemType1 . item01 must be there to get its 1 full point. item02 must be there to get 1 full point, but for item03 and item04, either of them (anyone of them) should be there to get its 1 full point. and for item05, it must be there to get its 1 full point. 
For itemType2. Item10 must be there to get its 1 full point. but item11, item12, item13 and item14 any 2 of them must be there to get its 2 full point. 
Each item type gets a different score in each channel. This is the ItemType and Channel target Points table

ChannelTypeTarget Max Point
Channel AItemType14
Channel AItemType23
Channel BItemType14
Channel BItemType23

in this case: ItemType1 in Channel A can get maximum point of 4. and Type 2 gets 3 points. because from the ItemGrouping table: in ItemType 1 we have  5 SKUs and two of those SKUs together can get 1 point.

so our FactTable (the transaction table) where auditors input their true or false answers:

CustomersTypeSKUChannelTrueOrFalse
Customer001ItemType1Item01Channel A1
Customer001ItemType1Item02Channel A1
Customer001ItemType1Item03Channel A0
Customer001ItemType1Item04Channel A1
Customer001ItemType1Item05Channel A1
Customer001ItemType2Item10Channel A1
Customer001ItemType2Item11Channel A0
Customer001ItemType2Item12Channel A0
Customer001ItemType2Item13Channel A1
Customer001ItemType2Item14Channel A1
Customer109ItemType1Item01Channel B1
Customer109ItemType1Item02Channel B1
Customer109ItemType1Item03Channel B1
Customer109ItemType1Item04Channel B1
Customer109ItemType1Item05Channel B1
Customer109ItemType2Item10Channel B0
Customer109ItemType2Item11Channel B0
Customer109ItemType2Item12Channel B1
Customer109ItemType2Item13Channel B0
Customer109ItemType2Item14Channel B1



So the output I want is :

CustomersChannelTypePoints
Customer001Channel AItemType14
Customer001Channel AItemType23
Customer109Channel BItemType14
Customer109Channel BItemType22


Hope I made it clear. Please feel free to ask me anything if you have any questions. 

P.S. At the moment we only have the FactTable (Transaction table). I am thinking to create "ItemGrouping table" and "ItemType and Channel target Points table" to make this output happen. I thought it is possible to do this calculation based on these tables. 
Edit: it means to get this outcome we can edit or change anything except for the fact table (transaction table)

P.S. we do have more items. the items in the ItemGrouping tables there are the chosen or Target SKUs. 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@datatuv I hope the sample data has a little error in the group? For item type 2 on channel B, is element 14 intended to be SKU group 2 or 1? What about point 10 on Channel B?

See my attachment below the signature, you need to organize the pools as it has some different fixes in progress, but basically I created a destination for each SKU group, then created a DAX to add the true/false for each SKU group for each client and compare it to the destination.

Let me know if he's around and what questions you have. 🙂


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

4 REPLIES 4
AllisonKennedy
Super User
Super User

@datatuv I hope the sample data has a little error in the group? For item type 2 on channel B, is element 14 intended to be SKU group 2 or 1? What about point 10 on Channel B?

See my attachment below the signature, you need to organize the pools as it has some different fixes in progress, but basically I created a destination for each SKU group, then created a DAX to add the true/false for each SKU group for each client and compare it to the destination.

Let me know if he's around and what questions you have. 🙂


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

I changed
SUM of (TrueOrFalse)
to
if(selectedvalue(Fact[AgendaName] IN VALUES('Agenda Calculation'[Name]), SUM(True False without Focus SKU), SUM(TRUE FALSE)))) 

and it worked. Thank you very much! ! !  🙏
I would like to buy you a couple of cups of coffee as a thank you!. (Please send me your Paypal if you are comfortable with it) (Please don't get me wrong. I just want to thank you a lot. You helped me save my job).
I am literally jumping over here. 

Anonymous
Not applicable

@AllisonKennedy  Wooaw. I would like to bow to you . its almost what I wanted. Its so close. but in channel B Customer109 item type 2 has a wrong score. it should be 2. 

for your questions. 
Channel B , Item Type2, Item14, is ItemGroup 2. 
Channel B, Item Type 2, Item10, is ItemGroup 1. 

Channel B , ItemGroup 2 has only 1 item which is item14
Channel B, ItemGroup 1 has 4 items which are item10, item11, item 12, item13. These 4 items together will get maximum of 2 scores. if there is 1 item it will get 1 score, if there are 2 or more(if 3, if 4) items it will get 2 scores

so the result for customer109 should be 2 scores for itemtype2 and 4 scores for itemtype 1

Anonymous
Not applicable

🙏

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.