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

     

     

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
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

 

 

  • vmsouza30's avatar
    vmsouza30
    Helper I
    9 years ago

    The solution it´s ok, great!!!!