Powerpivot Banding Based on a Category

[ohnow]

Hello,
I’ve been searching for an answer for this for 2 days and cannot find a solution or I am not able to understand one if I see it. I’m new to DAX.

I’m trying to create an end product of a Powerpivot report showing sales by: Product Category, Product Number, Product Sales Price, Sales Price Band. A Sales Price Band is a text description like “$0-$100”.

The tricky part is having about 6 Product Categories with each having unique bands. I had a Band Table showing:

Product Category	Band Description	Min	Max
Cat 1
Cat 2

<tbody>
</tbody>

I wasn’t sure if I could use one table so I then broke each Category Banding into its own table. One of the band table names is “FT_Chains”.
There is another table with Sales Data (table is called “FT_Sales”) with data such as:

Product Number	Sales Qty	Sales Price	Product Category
A1234	10	100	Cat 1
B1234	20	50	Cat 2

<tbody>
</tbody>

There is another table with Product Data (called “FT_Items”) such as:

Product Number	Descr	Product Category
A1234	Widget 1	Cat 1
B1234	Widget 2	Cat 2

<tbody>
</tbody>

The sales Data Table uses this formula to pull the Product Category from the Product Data table: =if(isblank(related(DT_Items[Product_Group01])),"2017 or not finished good item",related(DT_Items[Product_Group01]))

I’m using this formula to try to lookup the correct price band: =calculate(values(FT_Tier_Chains[Tier]),filter(FT_Tier_Chains,FT_Sales[Price]>FT_Tier_Chains[Min]&&FT_Sales[Price]<=FT_Tier_Chains[Max]))

That formula pulls the correct band from the FT_Chains banding table but Chains are only one of the many product categories where I need to look up the correct banding by product category.

Can anyone say whether I should have a table for each Product Category banding versus one table with all Categories and their banding?

Can anyone help finish the formula to first look up the banding based on the Product Category?
Thank you so VERY much for any help or thoughts!

 

[VBA Geek]

Take a look at: Static Segmentation

 

[ohnow]

Take a look at: Static Segmentation

Thanks for the help VBA Geek! I read the link you suggested and the thread on Dynamic Segmentation. If I understand correctly, those examples use one banding table whereas I'm stuck because I have multiple banding tables depending on the Product Group. Perhaps I do not understand.

It looks like I'm using the same formula they suggest, and it does work when I point to only one of the banding tables I have. I've tried different variations of formulas using Calculate, Filter and Switch but cannot get it to work. I think I need to somehow nest a FILTER, IF or SWITCH statement to say if the Product Group of the Sales Table equals Group One then use the Group One Banding Table otherwise try the remaining Banding Tables?

I might be going about this totally wrong since I'm new to this. I understand Parent/Child relationships in the world of bills-of-material, but not DAX. Maybe I need that? Thanks again for the help!

 

[VBA Geek]

If the banding tables have the same structure then you could merge them all into one, adding one KEY column which could be used to link them to the Product Categories


calculated column in products:

=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">VALUES</span><span class="Parenthesis" style="color:#969696"> (</span> Bands[Description] <span class="Parenthesis" style="color:#969696">)</span>,<br>    Bands[Product Category] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> Products[Product Category] <span class="Parenthesis" style="color:#969696">)</span>,<br>    Bands[Min] <= <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> Products[Sales Price] <span class="Parenthesis" style="color:#969696">)</span>,<br>    Bands[Max] > <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> Products[Sales Price] <span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>

 

[ohnow]

THANK YOU VBA GEEK!!!! This was killing me I hope what I ask is ethical on this board. . . I have two pivot table results giving something different than I expect or need. One of the results I've seen many times before and have never been able to find why it's happening. Using MrExcel messages, or I could post the problems, could you help me with them? I would be glad to make a Paypal donation to you for your time. Thank you again for your help with this one!!

 

[VBA Geek]

No problem you can start another thread with your problem

 

[ohnow]

Thank you! I've actually posted one of them under the title "Two Data Source Pivot Returning Field Total Sum Instead of Individual Values".

I'll have to post the other one a little later. I've heard we cannot post links so I hope giving the title will allow you to find it. If there's a better way, just let me know. I don't want to take any more of your time than necessary. I sincerely appreciate your help!