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 have a simple matrix that shows the Purchased amount (measure) of product and its respective percentage of total by 8 different fields.
Purchased = SUM(Purchased[Purchased])
These 8 fields are incorprated in a parameter.
Parameter name: "Field Reorder IMPS"
Field Reorder IMPS = {
("Market", NAMEOF('Purchased'[Market]), 0),
("Daypart", NAMEOF('Purchased'[Daypart]), 1),
("Week", NAMEOF('Purchased'[Week]), 2),
("Length", NAMEOF('Purchased'[Length]), 3),
("Program", NAMEOF('Purchased'[Program]), 4),
("Affiliate", NAMEOF('Purchased'[Affiliate]), 5),
("Genre", NAMEOF('Purchased'[Genre]), 6),
("Hour Start", NAMEOF('Purchased'[Hour Start]), 7)
}
I want the Purchased % (Percentage of column total) to dynamically calculate on a subtotal level.
Currently, when you select your Field Parameters it calculates the percentage based on the total, rather than subtotal amount. In the example below you can see that Abilene and Albany sum up to 100% as they are on the highest level of hierarchy. But if you drill down to the Daypart level you can see the percentages are not based upon the Market total, rather the overall total. In Abilene the "AV" should be 52.70/2293.31. In this case it is doing 52.70/6192.32.
I need to create a new measure to calculate the percentages based on the subtotals. I would like it to look like the following:
*Note* The [Field Reorder IMPS] parameter is in a slicer that allows the user to select whatever order they want to see the rows shown on the matrix. For example, if I select "Week" then "Daypart" then "Market" the matrix changes to that hierarchical order.
Not even sure where to begin here so any help would be great!
I've got bad news for you. There is no way in Power BI to retrieve the order in which fields of a field parameter have been selected by the user. That means you won't know what is top level of a hierarchy and what isn't.
Well known limitation with no obvious solution any time soon.
If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com/?forum=2d80fd4a-16cb-4189-896b-e0dac5e08b41
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
86 | |
77 | |
60 | |
58 | |
58 |
User | Count |
---|---|
151 | |
113 | |
99 | |
80 | |
72 |