Forum Discussion

vmsouza30's avatar
vmsouza30
Helper I
9 years ago
Solved

Calculation with the variables

In my table there is a grouping (Group  1,2,3) composed of several clients that are not repeated and the only field that is modified is the field "FACTOR".   Since my base client is client "E", and...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    vmsouza30

     

    Hi,

     

    Maybe you could try these formulas.

     

     

    Above =
VAR E2014 =
    CALCULATE (
        MIN ( Table1[Factor2014] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" )
    )
VAR E2015 =
    CALCULATE (
        MIN ( Table1[Factor2015] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" )
    )
VAR E2016 =
    CALCULATE (
        MIN ( Table1[Factor2016] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" )
    )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            'Table1'[Factor2014] > E2014
                || 'Table1'[Factor2015] > E2015
                || 'Table1'[Factor2016] > E2016
        )
    )
    Below =
VAR E2014 =
    CALCULATE (
        MIN ( Table1[Factor2014] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" )
    )
VAR E2015 =
    CALCULATE (
        MIN ( Table1[Factor2015] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" )
    )
VAR E2016 =
    CALCULATE (
        MIN ( Table1[Factor2016] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" )
    )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            'Table1'[Factor2014] < E2014
                || 'Table1'[Factor2015] < E2015
                || 'Table1'[Factor2016] < E2016
        )
)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

     

     

LairdLight's avatar
LairdLight
Frequent Visitor
9 years ago

I agree with the other posts suggesting you reshape your data to have a single factor column and a year date dimension.

 

Having said that I think you're after something like this, I created a table like yours called Factor...

 

Then I created a new column called "Clientes Factor" which totals the three factor values for each client:

Clientes Factor = CALCULATE(SUMX(factor, factor[Factor 2014]+factor[factor 2015]+factor[factor 2016]))

 

Then I created a new measure called "Factor E Calc" which is Client Es factor total for comparison:

Factor E Calc= calculate(SUMX(factor, factor[Factor 2014]+factor[factor 2015]+factor[factor 2016]), Factor[clientes]="E")

 

Then to work around a circular dependency error I created a new measure called "Factor E" which is equal to the value I entered manually (copying the value calculated in "Factor E Calc):

Factor E = 6.1

 

Then I created a new column comparing "Clientes Factor" with "Factor E" I called it Factor Rating:

Factor Rating = if(Factor[Clientes Factor]<Factor[Factor E], "Less", (if(Factor[Clientes Factor]=Factor[Factor E], "Same", "Higher")))

 

Here's what the resulting table looks like:

groupclientesFactor 2014Factor 2015Factor 2016Clientes FactorFactor Rating
1A122.35.3Less
1B1.53.41.36.2Higher
1C2.311.75Less
1D1.31.51.34.1Less
2E1.722.46.1Same
2F241.57.5Higher
2G3.41.72.27.3Higher
2H12.325.3Less
3I1.51.32.45.2Less
3J21.714.7Less
3K42.42.48.8Higher
3L1.74.728.4Higher

 

I'm sure there's more elegant ways of achieving the same outcome but hopefully that helps :)