Forum Discussion

rauerfc's avatar
rauerfc
Frequent Visitor
6 years ago
Solved

Measure with filter

Hello,   I'm calculating the weight on the week divided by the weight of the month, and my KPI is the average of their percentages by month. There is anyway that I could do this as measure that wil...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi

     

    I solved it with 2 measures (which probably can be simplified to 1)

    Measure = 
VAR __monthTotal =
    CALCULATE(
        SUM( 'Table'[Weight]);
        ALLEXCEPT( 'Table'; 'Table'[Month])
    )
RETURN
    DIVIDE(
        SUM( 'Table'[Weight]);
        __monthTotal;
        0
    )
Measure 2 = 
IF(
    ISFILTERED( 'Table'[Month]);
    [Measure];
    AVERAGEX(
        SUMMARIZE(
            'Table';
            'Table'[Month];
            'Table'[Week];
            "measure"; [Measure]
        );
        [measure]
    )
)

    If this works then please accept it as the solution, kudos is also appreciated.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi

 

I solved it with 2 measures (which probably can be simplified to 1)

Measure = 
VAR __monthTotal =
    CALCULATE(
        SUM( 'Table'[Weight]);
        ALLEXCEPT( 'Table'; 'Table'[Month])
    )
RETURN
    DIVIDE(
        SUM( 'Table'[Weight]);
        __monthTotal;
        0
    )
Measure 2 = 
IF(
    ISFILTERED( 'Table'[Month]);
    [Measure];
    AVERAGEX(
        SUMMARIZE(
            'Table';
            'Table'[Month];
            'Table'[Week];
            "measure"; [Measure]
        );
        [measure]
    )
)

If this works then please accept it as the solution, kudos is also appreciated.

  • rauerfc's avatar
    rauerfc
    Frequent Visitor
    6 years ago

    Hey Anonymous ,

     

    It worked with this small adaptation:

     

    Measure = 
VAR __monthTotal =
    CALCULATE(
        SUM( 'Table'[Weight]);
        ALLEXCEPT( 'Table'; 'Table'[Month];'Table'[Plant])
    )
RETURN
    DIVIDE(
        SUM( 'Table'[Weight]);
        __monthTotal;
        0
    )
Measure 2 = 
IF(
    ISFILTERED( 'Table'[Month]);
    [Measure];
    AVERAGEX(
        SUMMARIZE(
            'Table';
            'Table'[Month];
            'Table'[Week];
            "measure"; [Measure]
        );
        [measure]
    )
)

     Thanks!

     

     

  • rauerfc's avatar
    rauerfc
    Frequent Visitor
    6 years ago

    Hi Anonymous 

    It's working when Looking at Macro, but when it is filtered by Plant, the total is divided by the total of the month, I think that I didn't explain this before, but what I want is that when is filtered by Plant the  KPI is recalculated by:

    SUM(Weight produced by the plant on the week)/Sum(Weight produced by the plant on the Month).