Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 ID | Group ID | Category ID | Units Sold |
A | 010101 | 0101 | 01 | 2 |
B | 010201 | 0102 | 01 | 4 |
C | 020101 | 0201 | 02 | 3 |
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.
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.
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...
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
27 | |
24 | |
23 |