How to calculate the combination of two layers selection in BI

[naoyixue1]

Hey all,

I have a problem to create 2 layers' hierarchy calculation ( combination of 2 layer selection).

Since we changed the way to calculate the inventory, I have to keep 2 ways calcualated inventorys and related WOS (week of Supply) in the report.

When I create the original repot, I added ' inventory' , 'WOS (week of Supply)', and 'Next Supply' in one selection. And based on the selection, I create the measure. So user can choose whatever they want to see in the table. (see below table in BI and related way how I calculated that)

Right now, organization wants to see both inventory w/ Quality on hold volume and w/o Quality on hold volume. Given that, I'm thinking whether I can apply the similar logic to create that table in BI (see below)

Technically, I will have two selections. The calculation should reflect the combination of those two selection('w/o Quality On Hold' ' w/ Quality On Hold assocatied with 'Inventory', 'WOS' and 'Next Supply''.  Just like 2 drill down selections. Firstly, we can select 'w/o Quality on Hold' or 'w/ Quality on Hold', and then select  'Inventory', 'WOS' and 'Next Supply'. I'm not sure how I should develop the measure to combine them together with selectvalue dax...or Can I use the other functions to calculate that combination hierarchys. Any thoughts? Thanks!

[naoyixue1]

@HoangHugo I mean how I can make sure as long as user select both "w/ and W/o" selection and the measure ' WOS, Inventory and Next Supply), then user can see the number based on those combinations. Thanks! 

[naoyixue1]

@HoangHugo Sure.  See below. That's the sample of part of fact table, where I calculated the On Q.

Originally, my total inventory = On Q + On Hand. But currently, organization wants to see both inventory w/ and w/o On Q. In other words. I need to have two measures to calculate the inventory.

Inventory 1 = On Q + On hand (same as before)

Inventory 2 = On Q

Given that, i will have two differernt value of the same measure of Week of Supply, which is calculated based on the inventory. 

When I previously had one measure of Inventory( I just applied selectvalue to show the data whatever user select Week of Supply or Inventory. but now at the beginning I have two different Inventory and WOS. How can I add something in the report but still keep the same 2nd layer selection (Week of Supply & Inventory) for user to select whatever they want to see. In other words, How can I support user to select the combination w/ On Q assoicated with Inventory or Week of Supply and w/o On Q assoicated with Inventory or Week of Supply. 

[HoangHugo]

Ok, Understood

Creating a slection "w and w/o On Q" is required to user can choose.

Modify a bit your mearsure of invetory

Inventory = 

var select = SELECTEDVALUE(secltion w and w/o)

return

 Inventory = if(select="without On Q", 0, On Q) + On hand
That's it, when user choose "w or w/o ON Q", Invetory will change as well.

[naoyixue1]

@HoangHugo Thanks for your reply. I know that. Since in overall the report, I want to give the option for the user to see inventory, next supply and week of supply. Given that, I already added one selectvalue dax to catch those 3 measures. (see below). User can select each of those three to reflect the number in the table. Based on what you said, I'm not sure whether I should separete the selectevalue firstly. In other words. set the inventory based on 2 conditions (w/ and w/o On Q)

and then still apply my original dax to have that combination (Inventory w/ or inventory w/o On Q) 

[HoangHugo]

Hi

The attribute "w/Quality on hold" and"w/o Quality on hold" is one column in your data table, right? if so, just create a slicer, and put this column into. You can seet "Select All" in Slicer for user.

[naoyixue1]

No, that's the part of my measure. That's why I have to create two measures with or without the Quality on Hold volume.

Any thoughts, guys? Thanks!

[HoangHugo]

Ok, but I cannot see which measures related or used "w/Quality on hold" and"w/o Quality on hold". Or how to defined "w/Quality on hold" and"w/o Quality on hold". Or maybe I can not understand your context. Can you show detail?