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-UOM Issue.JPGFig1 - Display columns of all UOMsFig2-UOM Issue - Columns Only with Liters.JPGBased on user selection, only display Liters columns

 

Regards

Prasad

 

 

7 REPLIES 7
Super User IV
Super User IV

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

Hi,

 

Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Prasad1 Helper I
Helper I

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

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

Super User IV
Super User IV

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

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/
Microsoft v-ljerr-msft
Microsoft

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

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

Prasad1 Helper I
Helper I

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

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

Super User IV
Super User IV

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

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/
Prasad1 Helper I
Helper I

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

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors