Hello,
I'm trying to create a dynamic visual that will allow me to show total count and percentantage of monthly total per subset per month, with both "percent or grand total" and the specific subset being selectable using a field parameter. It all works fine for total counts, but I'm having trouble calculating the percentage and getting the denominator to reference only the monthly total and not the grand total.
I can get it to work through brute forcing using selectedvalue() and a switch statement for the denominator where it calculates the countrows with different removefilter() statements depending on which field parameter value is selected (below), but that feels very clunky and also difficult to upkeep if the end users end up wanting more fields added to the list of allowable legends.
I've also tried using allexcept(), but then it ends up not functioning properly if it's being filtered by anything else on the page. The x axis is a date hierarchy on a date field on the same table.
Any advice would be greatly appreciated!
Percent of Quotes =
var field = SELECTEDVALUE(Legend[Legend Fields])
var fieldname = switch(true(),
CONTAINSSTRING(field,"Subset1"),"Subset1",
CONTAINSSTRING(field,"Subset2"),"Subset2",
CONTAINSSTRING(field,"Subset3"),"Subset3",
CONTAINSSTRING(field,"Subset4"),"Subset4")
var num =
countrows('Table')
var denom =
switch(fieldname,
"Subset1",
CALCULATE(
countrows(
'Table'),
REMOVEFILTERS(
'Table'[Subset1]
)
),
"Subset2",
CALCULATE(
countrows(
'Table'),
REMOVEFILTERS(
'Table'[Subset2]
)
),
"Subset3",
CALCULATE(
countrows(
'Table'),
REMOVEFILTERS(
'Table'[Subset3]
)
),
"Subset4",
CALCULATE(
countrows(
'Table'),
REMOVEFILTERS(
'Table'[Subset4]
)
)
)
return divide(num,denom,0)
