Measure with filter

[rauerfc]

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 will allow me filter by plant?

Table:

Year	Plant	Month	Week	Weight	Date	Concat
2019	PMRI	9	W1	1299,695	01/09/2019	W1September
2019	PMRI	9	W2	1462,354	01/09/2019	W2September
2019	FACF	10	W1	901,005	01/10/2019	W1October
2019	LDCI	9	W4	2961,658	01/09/2019	W4September
2019	PACI	11	W1	8665,756	01/11/2019	W1November
2019	PACI	9	W1	8665,756	01/09/2019	W1September
2019	BMCI	10	W1	3739,291	01/10/2019	W1October
2019	PACI	10	W1	8665,756	01/10/2019	W1October
2019	PACI	10	W3	5548,825	01/10/2019	W3October
2019	PACI	12	W1	8665,756	01/12/2019	W1December
2019	BMCI	12	W1	3739,291	01/12/2019	W1December
2019	PACI	9	W4	1743,095	01/09/2019	W4September
2019	PACI	10	W4	1743,095	01/10/2019	W4October
2019	BMCI	9	W1	3739,291	01/09/2019	W1September
2019	BMCI	10	W4	1555,722	01/10/2019	W4October
2019	LDCI	10	W4	2961,658	01/10/2019	W4October
2019	PACI	11	W3	5548,825	01/11/2019	W3November
2019	PACI	12	W3	5548,825	01/12/2019	W3December
2019	PACI	10	W2	4448,179	01/10/2019	W2October
2019	LDCI	11	W4	2961,658	01/11/2019	W4November
2019	VLCI	10	W4	73,602	01/10/2019	W4October
2019	VLCI	11	W4	73,602	01/11/2019	W4November
2019	VLCI	10	W3	62,526	01/10/2019	W3October
2019	LDCI	9	W3	8200,935	01/09/2019	W3September
2019	PECI	9	W3	84,893	01/09/2019	W3September
2019	PECI	10	W3	84,893	01/10/2019	W3October
2019	PECI	11	W4	147,708	01/11/2019	W4November
2019	PMCI	12	W1	1182,982	01/12/2019	W1December
2019	PMRI	11	W4	946,721	01/11/2019	W4November
2019	PECI	10	W2	203,629	01/10/2019	W2October
2019	PMCI	9	W1	1182,982	01/09/2019	W1September
2019	VLCI	9	W3	62,526	01/09/2019	W3September
2019	VLCI	9	W1	143,143	01/09/2019	W1September
2019	VLCI	10	W2	191,504	01/10/2019	W2October
2019	BMCI	10	W3	7783,261	01/10/2019	W3October
2019	BMCI	11	W4	1555,722	01/11/2019	W4November
2019	BMCI	12	W3	7783,261	01/12/2019	W3December
2019	BMCI	11	W2	1057,279	01/11/2019	W2November
2019	BMCI	12	W2	1057,279	01/12/2019	W2December
2019	VLCI	9	W2	191,504	01/09/2019	W2September
2019	PECI	9	W2	203,629	01/09/2019	W2September
2019	VLCI	12	W4	73,602	01/12/2019	W4December
2019	PECI	9	W4	147,708	01/09/2019	W4September
2019	PECI	9	W1	148,464	01/09/2019	W1September

Desire output:

SUM(Weight of Week "1" on January)/SUM(Weight on January) %

SUM(Weight of Week "2" on February)/SUM(Weight on February) %

SUM(Weight of Week "3" on March)/SUM(Weight on March) %

Example:

Week	Sep	Oct	Nov	Dec	Jan	Feb	KPI
W1	34%	33%	32%	35%	18%	23%	29%
W2	28%	27%	27%	29%	29%	28%	28%
W3	26%	28%	29%	31%	39%	39%	32%
W4	12%	13%	12%	6%	15%	11%	11%

[Anonymous · Accepted solution]

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]

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]

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).