Calculation problem

[Anonymous]

Hello,

I am having the below mentioned formula.

FrameContractGrouping = IF([InvoiceUsage]<=0,1;"0-10%";

IF(AND([InvoiceUsage]<=0,2;[InvoiceUsage]>0,1);"10-20%";

IF(AND([InvoiceUsage]<=0,3;[InvoiceUsage]>0,2);"20-30%";

IF(AND([InvoiceUsage]<=0,4;[InvoiceUsage]>0,3);"30-40%";

IF(AND([InvoiceUsage]<=0,5;[InvoiceUsage]>0,4);"40-50%";

IF(AND([InvoiceUsage]<=0,6;[InvoiceUsage]>0,5);"50-60%";

IF(AND([InvoiceUsage]<=0,7;[InvoiceUsage]>0,6);"60-70%";

IF(AND([InvoiceUsage]<=0,8;[InvoiceUsage]>0,7);"70-80%";

IF(AND([InvoiceUsage]<=0,9;[InvoiceUsage]>0,8);"80-90%";

IF(AND([InvoiceUsage]<=1;[InvoiceUsage]>0,9);"90-100%";

IF(AND([InvoiceUsage]<=2;[InvoiceUsage]>1);"100-200%";

IF([InvoiceUsage]>2;"over 200%"))))))))))))

Unfortunately, it is not working properly because if choose one of the groups. For example - "40-50%" it is showing rows that are out of the scope of this filter. 

[Anonymous]

[Anonymous]

Hi @Anonymous ,

It seems like that contract has multiple records with same category range, so its usage records has been summarized on your visual.(notice: category column not effect your filter, I haven't found you use all/all excel functions to ignore current filter)

Regards,

Xiaoxin Sheng

[Anonymous]

@Anonymous 

Can you be more specific as this general info for sure is not helping me... How come that my calculation is working and then the grouping based on the same calculation is totally wrong. What exactly I need to do in order the column "FrameContractGrouping" to have the proper group ....

[Anonymous]

HI @Anonymous ,

You can try to use following calculated column to convert invoice usage as group:

FrameContractGrouping =
IF (
    [InvoiceUsage] > 2;
    "over 200%";
    IF (
        AND ( [InvoiceUsage] <= 2; [InvoiceUsage] > 1 );
        "100-200%";
        IF (
            AND ( [InvoiceUsage] <= 1; [InvoiceUsage] > 0,9 );
            "90-100%";
            ROUNDDOWN ( [InvoiceUsage]; 1 ) * 100 & "-"
                & ( ROUNDDOWN ( [InvoiceUsage]; 1 ) + 0,1 ) * 100 & "%"
        )
    )
)

What type of 'invoice Usage' field you used? column or measure? Current you can't create a dynamic calculated column based on slicer/filter or dynamic measure.

Regards,

Xiaoxin Sheng