Summarization of weighted average

[SinduN]

I’m trying to get the weighted calculation to work for the totals row. 

The Delivery Qty in SU is only available for blank forecast dates. So in order to show the delivery qty for all dates and consider the filters and row context I have formula below for totalsales.

Totalsales = CALCULATE(SUM('V_FORECASTACCURACY'[DeliveryQty in SU]),ALLEXCEPT(V_FORECASTACCURACy,V_FORECASTACCURACY[PROFITCENTERBRAND],V_FORECASTACCURACY[STARTMONTH],V_FORECASTACCURACY[STARTWEEk],US_MATERIAL[Product Line]),allselected('US_MATERIAL'[Product Line]))

Weighted = divide([BaseHist], [Totalsales])

Weighted column works as expected. It takes the qty for both the product line selected and is giving the weighted average in individual rows and totals column.

The totals column for Weighted Terra Accuracy seems to use the total sakes per product line and not the total sales of both the product lines selected. the total sales of both the product lines selected.

Weighted Terra Accuracy = if(HASONEVALUE(US_MATERIAL[GTIN]),

[TerraAccuracyitemlevel]*[Weighted],

sumx(SUMMARIZE(V_FORECASTACCURACY,US_MATERIAL[GTIN],US_MATERIAL[Material Text],US_PRODUCTLINE[Product Line],"totals",([TerraAccuracyitemlevel]*[Weighted])),[totals]))

How can I make sure that the total sales is totalsales of both the product lines selected and not consider the row context of product line

[v-yuezhe-msft]

@SinduN,

Could you please share sample data of your tables and post expected result based on sample data? You can follow the guide in the blog below to share data.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,
Lydia

[SinduN]

Hello Lydia, Thank you for the reply.

I have a direct query on ssas model which has fact  table with multiple sources of data.

Basehist comes from sales as source and doesnt have a forecast date. 

I also have data with Terra as a source and it has multiple forecast dates.

I have the following formula for basehist so that the Delivery Qty shows a nonzero value even though the Forecast date is selected in slicers/table . The business would eventually use the terraaccuracy as the KPI with different dates in a chart.  

BaseHist = CALCULATE(SUM('V_FORECASTACCURACY'[DeliveryQty in SU]), ALLEXCEPT(V_FORECASTACCURACy,V_FORECASTACCURACY[PROFITCENTERBRAND],V_FORECASTACCURACY[STARTMONTH],US_MATERIAL[Material Text],V_FORECASTACCURACY[STARTWEEk],US_PRODUCTLINE[Product Line],US_MATERIAL[GTIN],US_MATERIAL[Product Line],GLOBALSBU_TXT[Global SBU Text],DS_BRAND[Global SBU]))

Terrastored forecast is directly pulled from the fact table. 

Brand,material name and Dmd Unit are from the dimension tables of Material.

Last 3 columns in the table below shows the excel calculations for Terra Stored Forecast, terraAccuracyItemLevel and Weighted columns. My end goal is to have terra accuracy column populated with values from Terraaccuracyitemlevel column for all rows and sum (Weighted terra accuracy) column for the total row (highlighted in red).

Below are my powerBI formulae. They work when I have one product line selected in the slicer. However doesnt work when I have multiple selected. 

TerraAccuracyitemlevel =
VAR __History = [BaseHist]
VAR __Terraforecast = SUM('V_FORECASTACCURACY'[TRR-Forecast Qty])
RETURN
iF( NOT ISBLANK(__history),if(__History<>0,if((1-DIVIDE(abs( __history-__Terraforecast), __History))>0,(1-DIVIDE(abs( __history-__Terraforecast), __History)))))

Weighted =
divide([BaseHist], [Totalsales])

Weighted Terra Accuracy = if(HASONEVALUE(US_MATERIAL[GTIN]),

                                               [TerraAccuracyitemlevel]*[Weighted],                  sumx(SUMMARIZE(V_FORECASTACCURACY,US_MATERIAL[GTIN],US_MATERIAL[Material Text],US_PRODUCTLINE[Product Line],"totals",([TerraAccuracyitemlevel]*[Weighted])),[totals]))

Terra Accuracy = if(HASONEVALUE(US_MATERIAL[GTIN]),

                              if(and(isblank([TerraAccuracyitemlevel]),not(isblank([basehist]))),0,[TerraAccuracyitemlevel]),

                              sumx(SUMMARIZE(V_FORECASTACCURACY,US_MATERIAL[GTIN],US_MATERIAL[Material Text],"totals",([TerraAccuracyitemlevel]*[Weighted])),[totals]))

Brand	DmdUnit	Material  Name	BaseHist	Terras Stored Forecast	TerraAccuracyitemlevel	Weighted	Weighted Terra Accuracy	Terra Accuracy	TerraAccuracyItemLevel =MAX(0,IF(D2=0,0,1-(ABS(D2-E2)/D2)))	Weighted=D2/D$23	Weighted terra Accuracy = K2*J2
6	33200865278	AHPLD AHOC FRSH SCNT 40/215LD BKT DIS	175	174.28	99.59%	0	0.11%	99.59%	99.59%	0.00	0.11%
6	10033200060229	AHPLD AHOC TROPICAL BURST 4/80LD	7671	7721.48	99.34%	0.05	4.78%	99.34%	99.34%	0.05	4.78%
6	10033200065101	AHPLD AHOC FRSH SCNT 3/130L TALL	10630	12117.43	86.01%	0.07	5.74%	86.01%	86.01%	0.07	5.74%
6	10033200065132	AHPLD 4/80LD AHOC FRSH SCNT COMPACTION	12951	15549.51	79.94%	0.08	6.49%	79.94%	79.94%	0.08	6.49%
6	10033200065149	AHPLD CLN BURST 4/100LD	0	4.03		0		0.00%	0.00%	0.00	0.00%
6	10033200065200	AHPLD 5/58L FREE	1591	1585.88	99.68%	0.01	0.99%	99.68%	99.68%	0.01	0.99%
6	10033200065217	AHPLD CLN BURST 3/160L TALL	3020	2987.68	98.93%	0.02	1.87%	98.93%	98.93%	0.02	1.87%
6	10033200065231	AHPLD CLEAN BURST 5/58LD	0	2.79		0		0.00%	0.00%	0.00	0.00%
6	10033200065248	AHPLD 4/100L FREE	1842	1793.2	97.35%	0.01	1.12%	97.35%	97.35%	0.01	1.12%
6	10033200065255	AHPLD AHOC FRSH SCNT 5/45LD	2295	2192.78	95.55%	0.01	1.38%	95.55%	95.55%	0.01	1.38%
6	10033200941726	AHPLD ALPINE CLN 3/155L	12695	13289.98	95.31%	0.08	7.59%	95.31%	95.31%	0.08	7.59%
6	10033200941733	AHPLD AHOC FRSH SCNT 3/115LD	14575	14426.92	98.98%	0.09	9.05%	98.98%	98.98%	0.09	9.05%
6	10033200971952	AHPLD CMPLT CRISP CLEAN 1/290LD BKT	57120	33236.76	58.19%	0.36	20.85%	58.19%	58.19%	0.36	20.85%
6	10033200972003	AHPLD ALPINE CLN 2/185LD BKT	2093	2193.69	95.19%	0.01	1.25%	95.19%	95.19%	0.01	1.25%
6	10033200972775	AHPLD CLN BURST 5/3.58LB/40LD	5649	5840.01	96.62%	0.04	3.42%	96.62%	96.62%	0.04	3.42%
3	33200032601	AHSWS 6/55OZ	19150	20556.07	92.66%	0.12	11.13%	92.66%	92.66%	0.12	11.13%
3	10033200030208	AHSWS 12/55OZ	6662	5633.62	84.56%	0.04	3.53%	84.56%	84.56%	0.04	3.53%
3	10065333003309	AHSWS 6/3KG	1296	1327.75	97.55%	0.01	0.79%	97.55%	97.55%	0.01	0.79%
6	10033200065279	AHPLD AHOC FRESH SCENT 1/215LD BKT	0	0		0		0.00%	0.00%	0.00	0.00%
6	10033200065132	AHPLD AHOC FRSH SCNT 4/80LD COMPCTN IRC	0			0		0.00%	0.00%	0.00	0.00%
6	10033200942211	AHPLD AHOC ISLAND RAIN 3/8.87LB/115LD	0			0		0.00%	0.00%	0.00	0.00%
PowerBI Totals			159415	140634	88.22	1	168.8	168.80%
Totals			159415				80.00%	80.00%		1.00	80.00%

[v-yuezhe-msft]

@SinduN,

The above sample table doesn't contain all the fields you mention in the DAX formulas. Could you please share sample data of original tables and post expected result based on the sample data here?

It seems that you would need to add filter inside the sumx function of Terra Accuracy measure to check if product line is same. For more details, please take a look at this similar thread.

Terra Accuracy =
IF (
    HASONEVALUE ( US_MATERIAL[GTIN] ),
    IF (
        AND ( ISBLANK ( [TerraAccuracyitemlevel] ), NOT ( ISBLANK ( [basehist] ) ) ),
        0,
        [TerraAccuracyitemlevel]
    ),
    SUMX (
        SUMMARIZE (
            V_FORECASTACCURACY,
            US_MATERIAL[GTIN],
            US_MATERIAL[Material Text],
            "totals", ( [TerraAccuracyitemlevel] * [Weighted] )
        ),
        [totals]
    )
)

Regards,
Lydia

[SinduN]

Hello Lydia,

Thank you for the reply

US_Material[GTIN] is renamed as Dmd Unit in the table and US_Material[Material Text] is renamed as Material Name

I want the weighted to be base hist/sum(basehist) of all the US_Material[Product Line] selected in the slicer/filter . This is where I'm facing the problem. The formula works fune if one Product line is selected. when multiple product lines are selected the DAX formula Im using is giving the weight for that Product line and not all of the product lines:

The total weight for weighted average calculation is considering 27,108 for lines with product line 003 and 132,307 for lines with product line 006. I want all of the lines to use 159,415 (total of both the sales) for all the lines in this case. it looks like the row context of the product line is coming in picture in this case. How can I not consider the row context of product line but still consider the filter/slicer in the calculation. 

US_Material [Product Line]	US_Material [GTIN]	US_Material [Material Text]	BaseHist	Terras Stored Forecast	TerraAccuracyitemlevel	Weighted	Weighted Terra Accuracy	Terra Accuracy	TerraAccuracyItemLevel =MAX(0,IF(D2=0,0,1-(ABS(D2-E2)/D2)))	Weighted=D2/D$23	Weighted terra Accuracy = K2*J2
6	33200865278	AHPLD AHOC FRSH SCNT 40/215LD BKT DIS	175	174.28	99.59%	0	0.11%	99.59%	99.59%	0.00	0.11%
6	10033200060229	AHPLD AHOC TROPICAL BURST 4/80LD	7671	7721.48	99.34%	0.05	4.78%	99.34%	99.34%	0.05	4.78%
6	10033200065101	AHPLD AHOC FRSH SCNT 3/130L TALL	10630	12117.43	86.01%	0.07	5.74%	86.01%	86.01%	0.07	5.74%
6	10033200065132	AHPLD 4/80LD AHOC FRSH SCNT COMPACTION	12951	15549.51	79.94%	0.08	6.49%	79.94%	79.94%	0.08	6.49%
6	10033200065149	AHPLD CLN BURST 4/100LD	0	4.03		0		0.00%	0.00%	0.00	0.00%
6	10033200065200	AHPLD 5/58L FREE	1591	1585.88	99.68%	0.01	0.99%	99.68%	99.68%	0.01	0.99%
6	10033200065217	AHPLD CLN BURST 3/160L TALL	3020	2987.68	98.93%	0.02	1.87%	98.93%	98.93%	0.02	1.87%
6	10033200065231	AHPLD CLEAN BURST 5/58LD	0	2.79		0		0.00%	0.00%	0.00	0.00%
6	10033200065248	AHPLD 4/100L FREE	1842	1793.2	97.35%	0.01	1.12%	97.35%	97.35%	0.01	1.12%
6	10033200065255	AHPLD AHOC FRSH SCNT 5/45LD	2295	2192.78	95.55%	0.01	1.38%	95.55%	95.55%	0.01	1.38%
6	10033200941726	AHPLD ALPINE CLN 3/155L	12695	13289.98	95.31%	0.08	7.59%	95.31%	95.31%	0.08	7.59%
6	10033200941733	AHPLD AHOC FRSH SCNT 3/115LD	14575	14426.92	98.98%	0.09	9.05%	98.98%	98.98%	0.09	9.05%
6	10033200971952	AHPLD CMPLT CRISP CLEAN 1/290LD BKT	57120	33236.76	58.19%	0.36	20.85%	58.19%	58.19%	0.36	20.85%
6	10033200972003	AHPLD ALPINE CLN 2/185LD BKT	2093	2193.69	95.19%	0.01	1.25%	95.19%	95.19%	0.01	1.25%
6	10033200972775	AHPLD CLN BURST 5/3.58LB/40LD	5649	5840.01	96.62%	0.04	3.42%	96.62%	96.62%	0.04	3.42%
3	33200032601	AHSWS 6/55OZ	19150	20556.07	92.66%	0.12	11.13%	92.66%	92.66%	0.12	11.13%
3	10033200030208	AHSWS 12/55OZ	6662	5633.62	84.56%	0.04	3.53%	84.56%	84.56%	0.04	3.53%
3	10065333003309	AHSWS 6/3KG	1296	1327.75	97.55%	0.01	0.79%	97.55%	97.55%	0.01	0.79%
6	10033200065279	AHPLD AHOC FRESH SCENT 1/215LD BKT	0	0		0		0.00%	0.00%	0.00	0.00%
6	10033200065132	AHPLD AHOC FRSH SCNT 4/80LD COMPCTN IRC	0			0		0.00%	0.00%	0.00	0.00%
6	10033200942211	AHPLD AHOC ISLAND RAIN 3/8.87LB/115LD	0			0		0.00%	0.00%	0.00	0.00%
PowerBI Totals			159415	140634	88.22	1	168.8	168.80%
Totals			159415				80.00%	80.00%		1.00	80.00%

[SinduN]

Any help on this topic. Im not sure if the selections in the slicers are ignored because Im using summarize function.