Forum Discussion

shresthapuja's avatar
shresthapuja
New Member
3 years ago

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:








 

2 Replies

  • can use hasonefilter instead of isinscope function.

    Mark as accepted if works. 

  • I have similar case and I tried your measures as well. I have not found any solution for such cases as well. Hopefully someone will be able to help.