How to calculate subtotal using conditions in power bi

[Charu]

Hi Everyone,

I need to calculate excepted calculation for the subtotal values, sample table and condition are below

salestable

Type,Salesactual,Salestarget,costactual,costtarget

FF,59,112,10,0

FF,4,20,1,0

FF,62,70,1,0

FF-125-202-12-0(subtotal)

TT,58,112,10,0

TT,3,20,1,0

TT,61,70,1,0

TT-122-70-12-0

total 247-404-24-0

sum(salesactual) = 125 and sum(salestarget)=202

now the conditon is 

measure/calculated column = 1.IF(salestarget=0,0,IF((salesactual/salestarget)>1,IF(type="FF",1*30%,1*40%),IF(type="FF",salesactual/salestarget*30%,

salesactual/salestarget*40%))) 

2.

IF(costtarget=0,0,IF((costactual/costtarget)>1,IF(type="FF",1*30%,1*40%),IF(type="FF",costactual/costtarget*30%,

costactual/costtarget*40%))) 

when I use matrix visual subtotal option is there but based on that subtotal I would like to achieve this above condition to create one score column.

Please help me how to achieve this. I tried calculated column but it's taking the Total values not the subtotal values 

[v-jiascu-msft]

Hi @Charu,

What's the original data? Can you share the file? I can't create a subtotal in the Matrix. The measure I tried is as follows.

Measure =
IF (
    SUM ( Table1[salestarget] ) = 0,
    0,
    IF (
        SUM ( Table1[Salesactual] ) / SUM ( Table1[salestarget] )
            > 1,
        IF ( MIN ( Table1[Type] ) = "FF", 0.3, 0.4 ),
        IF (
            MIN ( Table1[Type] ) = "FF",
            SUM ( Table1[Salesactual] ) / SUM ( Table1[salestarget] )
                * 0.3,
            SUM ( Table1[Salesactual] ) / SUM ( Table1[salestarget] )
                * 0.4
        )
    )
)

Best Regards,

Dale

[vanessafvg]

@Charu can you demonstrate what your expected result is?

[Charu]

Need to calculate one score column based on jobType filter and it's related weightsges.

For example, in a table, there are some col(s) like

Job_type, V_actual,V_target,V_score,A_actual,A_target,A_score,B_actuals,B_target,B_score

Job_Types are FF,VV,CC,RR

I need to get

FF = (V_score+A_score+B_Score) =(need to acheive  less than 1 based on calculation)

VV=(V_Score+A_score+B_Score) = (need to acheive  less than 1 based on calculation)

CC=(V_Score+A_score+B_Score) = (need to acheive  less than 1 based on calculation)

RR=(V_Score+A_score+B_Score) = (need to acheive  less than 1 based on calculation)

V_Score, A_score, B_score I need to calculate something like below

V_score = 

IF(V_target=0,0,IF((sum(V_actual)/sum(V_target))>1,IF(type="FF",1*30%,1*40%),IF(type="FF",sum(V_actual)/sum(V_target)*30%,

sum(V_actual)/sum(V_target)*40%))) 

this calculation is taking column wise but I need the calculation to take the subtotal value.

which means based on job_type subtotal will be there so for each jobtype I need each score column by adding the N-num of fields like V_actual,v_target,B_actaul,B_target,A_actual,A_target so and so

I could check more than one filter condition in measure as well calculated column 

That is if job type =FF then sum(V_actual)/sum(V_target)*30% else sum(V_actual)/sum(V_target)*40%