cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Prasad1
Helper I
Helper I

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

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 UOMsFig1 - Display columns of all UOMsBased on user selection, only display Liters columnsBased on user selection, only display Liters columns

 

Regards

Prasad

 

 

7 REPLIES 7
Ashish_Mathur
Super User III
Super User III

Hi,

 

Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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. Smiley Happy

 

Regards

Hi,

 

You may refer to my solution in this workbook.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors