Summarize and Addcolumn related with DAX

[rauerfc]

Hello,

I need to calculate the average of percentages in a table:

in order to do this i used this measure:

perc_rel = 
VAR perc_relacao = CALCULATE(SUM(Table_output[Peso_total]))/[somw_total]
VAR semana = SELECTEDVALUE(Table_output[Week])
RETURN
IF (HASONEFILTER(Table_output[mes_remessa].[Month]);
    perc_relacao;CALCULATE(AVERAGEX(indicador_semana;indicador_semana[Column]);FILTER(indicador_semana;indicador_semana[Week]=Semana)))

But, when the data it's filtered with Plant, the total doesn't recalculate:

As u can see the percentage of Week by month change but the total remains the same, except when we don't have a value by month, the case of W3.

Then I've created another measure:

Two SUMMARIZE tables one with the denominator "denominador" and another with numerator "numerador". It's is possible to divide one for another with [mes_remessa].[month] as a key value of intersection between the two Variables "denominador" and  "numerador"? 

perc_rel = 
VAR perc_relacao = CALCULATE(SUM(Table_output[Peso_total]))/[somw_total]
VAR denominador = SUMMARIZE(Table_output;Table_output[mes_remessa].[Month];"denom";SUM(Table_output[Peso_total]))
VAR numerador = SUMMARIZE(Table_output;Table_output[mes_remessa].[Month];Table_output[Week];"nume";SUM(Table_output[Peso_total]))
VAR sp_matriz = ADDCOLUMNS(numerador;"indicador";[nume]/CALCULATE(SUMX(denominador;[denom]);FILTER(numerador;[mes_remessa].[Month]=(FILTER(denominador;[mes_remessa].[Month])))))
VAR semana = SELECTEDVALUE(Table_output[Week])

RETURN
IF (HASONEFILTER(Table_output[mes_remessa].[Month]);
    perc_relacao;CALCULATE(AVERAGEX(sp_matriz;[indicador]);FILTER(Table_output;[Week]=semana)))

[v-piga-msft]

Hi @rauerfc ,

I'm still a little confused about your scenario.

To understand your requirement better, could you share some data sample as table format and your desired output so that we could have a test on it?

Best Regards,

Cherry

[rauerfc]

Hi @v-piga-msft ,

Basically i'm calculating the weight on the week divided by the weight of the month, and the total on the "dashboard table" is the average of their percentages by month. When I filter by Plant ("Fábrica") the average total doesn't recalculate. 

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

Total = Average of this percentages @v-piga-msft 

See the table below:

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