Hi everyone, I am trying to integrate the ABC Classfication in Power BI, however, I am struggling with how to integrate filters. This is the formula I have used:
Amount ABC =
var Outer =
ADDCOLUMNS(
VALUES(Jobs_Details[CLIENT_CODE]),
"OuterValue", [Gross Profit]
)
var Inner =
ADDCOLUMNS(
ALL(Jobs_Details[CLIENT_CODE]),
"InnerValue", [Gross Profit]
)
return
CALCULATE(
[Gross Profit],
VALUES(Jobs_Details[CLIENT_CODE]),
FILTER(
CALCULATETABLE(
ADDCOLUMNS (
Outer,
"CumulatedPercentage", DIVIDE (
SUMX (
FILTER (
Inner,
[InnerValue] >= [OuterValue]
),
[InnerValue]
),
CALCULATE (
[Gross Profit],
VALUES (Jobs_Details[CLIENT_CODE])
)
)
),
ALL(Jobs_Details)
)
, [CumulatedPercentage] > CALCULATE( MIN('ABC Class'[LowerBoundary]) )
&& [CumulatedPercentage] <= CALCULATE( MAX('ABC Class'[UpperBoundary]) )
)
)
The issue is that when I select another filter such as a Rate Table Number so I could see all Class A client that uses a specifc pricing strategy, it will grouped all client under Class A while originally without that filter selected, the client is a Class C. Moreover, when I try to look for a specific client using the search function, I can see the client is duplicate with only information attached to one Class however, it is the wrong class too. Any idea how to fix this so it is dynamic no matter which filter is used? Thank you.
