Forum Discussion
Aggregation issue in hierarchy matrix visual Power BI
Here, I have a table named Sales which includes 5 columns. I have created a matrix visual with managername, branch, and clientname which are shown in hierarchy. Qualifiedcondition and NotQualifiedcondition are the columns calculated using measure.
In order to identify qualified/not qualified client we have following condition:
if sum of profit >= 4000 then qualified if sum of profit < 4000 then not qualified
Data shown in the bottom level of hierarchy is as required but when I drill up, the data from both columns get added i.e. the value of qualifiedcondition and notqualifiedcondition are summed which is not what we require. We want values to be added only of a particular column when drilled up. Please advise.
Measure for Qualified Condition
Qualified condition =
var _result =
switch(TRUE(),
ISINSCOPE(Sales[clientname]),
CALCULATE(
if(
sum(Sales[profit])>=4000,
sum(Sales[profit])
)
),
CALCULATE(
sum(Sales[profit])
)
)
return
if(ISBLANK(_result),0,_result)
Measure for Not Qualified Condition
Not qualified condition =
var _selectedclient = SELECTEDVALUE(sales[clientname])
var _result =
switch(TRUE(),
ISINSCOPE(sales[clientname]),
CALCULATE(
if(
sum(sales[profit])<4000,
sum(sales[profit])
)
),
CALCULATE(
sum(sales[profit])
)
)
return
if(ISBLANK(_result), 0, _result)
Result:
Expected: