Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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