Based on user selected Unit of Measure (UOM), showing relevant UOM columns only in the list

[Prasad1]

Hi

We have an inventory report that has stock calculations in different columns for different Unit of measures viz. In Liters, In Drums, In Cartons. Current report displays all columns with all Unit of measures (Fig1). The requirement is to have a Selection for UOM (Liters or Drums or Cartons) and display only columns relevant to the selected UOM. For e.g when user select Liters, it should only show columns with Liters (Fig2). And similarly for Drums and Cartons.

How do we dynamically select columns based on user selection? Thank you very much in advance.

Fig1 - Display columns of all UOMsBased on user selection, only display Liters columns

Regards

Prasad

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[Prasad1]

Hi

Thanks for the quick reply. This is the sample data for the illustration purpose.

https://drive.google.com/file/d/1KV3bUpaoPeIF2NUaj1VbHTQLACOISi0I/view?usp=sharing

Thanks

Prasad

[v-ljerr-msft]

Hi @Prasad1,

Have you tried the solution provided by @Ashish_Mathur above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[Ashish_Mathur]

Hi,

You may refer to my solution in this workbook.

Hope this helps.

[Prasad1]

Hi Ashish

Thanks for the reply.

Two points

1. The data transformations are correct and the final unpivoted query shows correct transformation values. But the Matrix visual is not displayed with correct values. Simple check is, Current stock (In any units) = Safety stock + Remaining stock. I checked that the values are correct in the transformed table. I guess values mixed up somehow.

2. Understood the page level filter that you used to select UOM. Assuming the above problem is fixed, selecting one UOM, let's say Liters, shows the Current Sales, Current Stock, Safety stock, Remaining stock in Liters in 4 columns. But when two UOMs are slelected, let's say, Liters and Drums together, the expectation is, is to show total 8 columns, with 2 columns for current sales in Liters and Drums next to each other, 2 columns for Current stock in Liters, Drums next to each other and 2 columns for Safety stock in Liters and Drums and so on. Similarly when all the three UOMs are selected, the report should show, 12 columns for 4 key figures with each UOM.

I just tried to select multiple values, and it is adding up everything shows sum numbers that make no sense to the user.

Is there a way to achieve the above visualization.

Thanks

Prasad

Understood the transformation of the table that you did by creating one row for each UOM for all the values. Page level filters are used to select any of Liters, Drums, Cartons values. OK, It gives the result as expected if user select one values. Perfect solution. But the requirement is to show columns if user selected one UOM, and to show two columns when users

[Ashish_Mathur]

Hi,

For Litres, for all Products, Current Stock = Remaining Stock + Safety stock.  I have checked each line item.  For Drums, this statement is not true.  But the problem is not with the visual or the transformations, it is with the Data.  A case in point being Product 10 - for this product, in your base data itself, Current Stock does not equal Remaining Stock + Safety stock.  Make the correction in your base data and the visual will be correct.

As regards your second concern, simply drag UoM to the Column labels (just above Attribute) and drill down the column labels, to show all levels (top left hand side controls of the Matrix visual).

Hope this helps.

[Prasad1]

Hi Ashish

Happy new year.

Thanks for identifying the data issue. Yes. You are right. Data in Liters is correct and others have an issue. Let me correct it and try it out and update.