Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mattira
Frequent Visitor

Filter a measure by values of selected field parameter

I want to use a parameter, formatted as a hierarchy slicer, to let the user select specific products to include in some calculations. The goal is to have two slicers built by parameters called CGM1 and CGM2 and then be able to use the quantity of units sold of each group and divide them by one another. More details below:

 

The data is formatted like:

Article Code (unique names)Model IDGroup IDCategory IDUnits Sold
A0101010101012
B0102010102014
C0201010201023

Where Category is the highest level in the hierarchy, then Group, then model, then Article Code.

I want the user to be able to select any Category, Group or Model in a parameter called "CGM1" and then select anything from another parameter which is built in the same way, but called "CGM2". I want to use these to group all products included in the selection and take the aggregated quantity of units sold from each group and divide them by one another.


Then I want to have a measure called "Hitrate" where I calculate the SUM of [Units Sold] from each parameter selection and divide CGM1 Units sold by CGM2 Units sold. I can't figure out how to do this using parameters, so please suggest any way to do this?

Using only the data example from the table above, the user would be able to select CGM1 = "01" (the category in this example) which would include Article Codes A and B and calculated to 6 units sold in total, then the user would select CGM2 = "020101" (model in this example) which would include Artice Code C and calculated to 3 units sold. The result would be:
CGM1 / CGM2 = Result
>>
6 / 3 = 2


The only way I have got Parameters to work in a measure is by using switch and hard coding names of the selections, but this has to be more dynamic, since there are hundreds of different Models, which means I can't use hard coded switch-functions. Any suggestions?

I create the CGM1 slicer by adding a normal field-parameter, add in Category ID, Group ID, Model ID and save it. Then I create a slicer by adding the CGM1 parameter I just created and then go to the data field and choose "Show values of selected field" like in the picture below.

Mattira_1-1691406821938.png

Now the users can select any value included in the Category/Group/Model hierarchy, even multiple ID's from any of the three dimensions added to the parameter.

 

2 REPLIES 2
Mattira
Frequent Visitor

Hi @Mahesh0016 and thank you for you reply, I am not sure I fully understand what you are requesting though, because I thought I wrote that in the initial post?

 

I want to divide CGM1 with CGM2 and the end output (Hitrate-calculation) would be used in, for example, a table where I have Store-names in the first column and hitrate-results in the second column. 

 

The data table is in reality 100+ times wider than my simple example table in the post above. Every Article has also row-information on in which store it was sold, on what timestamp and so forth...

 

 

Mahesh0016
Super User
Super User

@Mattira Please Can you Share your EndOutPut? Thank You!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.