Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SinduN
Helper I
Helper I

Summarization of weighted average

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

Capture.PNG

 

5 REPLIES 5
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

BrandDmdUnitMaterial  NameBaseHistTerras Stored ForecastTerraAccuracyitemlevelWeightedWeighted Terra AccuracyTerra AccuracyTerraAccuracyItemLevel =MAX(0,IF(D2=0,0,1-(ABS(D2-E2)/D2)))Weighted=D2/D$23Weighted terra Accuracy = K2*J2
633200865278AHPLD AHOC FRSH SCNT 40/215LD BKT DIS175174.2899.59%00.11%99.59%99.59%0.000.11%
610033200060229AHPLD AHOC TROPICAL BURST 4/80LD76717721.4899.34%0.054.78%99.34%99.34%0.054.78%
610033200065101AHPLD AHOC FRSH SCNT 3/130L TALL1063012117.4386.01%0.075.74%86.01%86.01%0.075.74%
610033200065132AHPLD 4/80LD AHOC FRSH SCNT COMPACTION1295115549.5179.94%0.086.49%79.94%79.94%0.086.49%
610033200065149AHPLD CLN BURST 4/100LD04.03 0 0.00%0.00%0.000.00%
610033200065200AHPLD 5/58L FREE15911585.8899.68%0.010.99%99.68%99.68%0.010.99%
610033200065217AHPLD CLN BURST 3/160L TALL30202987.6898.93%0.021.87%98.93%98.93%0.021.87%
610033200065231AHPLD CLEAN BURST 5/58LD02.79 0 0.00%0.00%0.000.00%
610033200065248AHPLD 4/100L FREE18421793.297.35%0.011.12%97.35%97.35%0.011.12%
610033200065255AHPLD AHOC FRSH SCNT 5/45LD22952192.7895.55%0.011.38%95.55%95.55%0.011.38%
610033200941726AHPLD ALPINE CLN 3/155L1269513289.9895.31%0.087.59%95.31%95.31%0.087.59%
610033200941733AHPLD AHOC FRSH SCNT 3/115LD1457514426.9298.98%0.099.05%98.98%98.98%0.099.05%
610033200971952AHPLD CMPLT CRISP CLEAN 1/290LD BKT5712033236.7658.19%0.3620.85%58.19%58.19%0.3620.85%
610033200972003AHPLD ALPINE CLN 2/185LD BKT20932193.6995.19%0.011.25%95.19%95.19%0.011.25%
610033200972775AHPLD CLN BURST 5/3.58LB/40LD56495840.0196.62%0.043.42%96.62%96.62%0.043.42%
333200032601AHSWS 6/55OZ1915020556.0792.66%0.1211.13%92.66%92.66%0.1211.13%
310033200030208AHSWS 12/55OZ66625633.6284.56%0.043.53%84.56%84.56%0.043.53%
310065333003309AHSWS 6/3KG12961327.7597.55%0.010.79%97.55%97.55%0.010.79%
610033200065279AHPLD AHOC FRESH SCENT 1/215LD BKT00 0 0.00%0.00%0.000.00%
610033200065132AHPLD AHOC FRSH SCNT 4/80LD COMPCTN IRC0  0 0.00%0.00%0.000.00%
610033200942211AHPLD AHOC ISLAND RAIN 3/8.87LB/115LD0  0 0.00%0.00%0.000.00%
PowerBI Totals  15941514063488.221168.8168.80%   
Totals  159415    80.00% 80.00% 1.0080.00%

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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] 

BaseHistTerras Stored ForecastTerraAccuracyitemlevelWeightedWeighted Terra AccuracyTerra AccuracyTerraAccuracyItemLevel =MAX(0,IF(D2=0,0,1-(ABS(D2-E2)/D2)))Weighted=D2/D$23Weighted terra Accuracy = K2*J2
633200865278AHPLD AHOC FRSH SCNT 40/215LD BKT DIS175174.2899.59%00.11%99.59%99.59%0.000.11%
610033200060229AHPLD AHOC TROPICAL BURST 4/80LD76717721.4899.34%0.054.78%99.34%99.34%0.054.78%
610033200065101AHPLD AHOC FRSH SCNT 3/130L TALL1063012117.4386.01%0.075.74%86.01%86.01%0.075.74%
610033200065132AHPLD 4/80LD AHOC FRSH SCNT COMPACTION1295115549.5179.94%0.086.49%79.94%79.94%0.086.49%
610033200065149AHPLD CLN BURST 4/100LD04.03 0 0.00%0.00%0.000.00%
610033200065200AHPLD 5/58L FREE15911585.8899.68%0.010.99%99.68%99.68%0.010.99%
610033200065217AHPLD CLN BURST 3/160L TALL30202987.6898.93%0.021.87%98.93%98.93%0.021.87%
610033200065231AHPLD CLEAN BURST 5/58LD02.79 0 0.00%0.00%0.000.00%
610033200065248AHPLD 4/100L FREE18421793.297.35%0.011.12%97.35%97.35%0.011.12%
610033200065255AHPLD AHOC FRSH SCNT 5/45LD22952192.7895.55%0.011.38%95.55%95.55%0.011.38%
610033200941726AHPLD ALPINE CLN 3/155L1269513289.9895.31%0.087.59%95.31%95.31%0.087.59%
610033200941733AHPLD AHOC FRSH SCNT 3/115LD1457514426.9298.98%0.099.05%98.98%98.98%0.099.05%
610033200971952AHPLD CMPLT CRISP CLEAN 1/290LD BKT5712033236.7658.19%0.3620.85%58.19%58.19%0.3620.85%
610033200972003AHPLD ALPINE CLN 2/185LD BKT20932193.6995.19%0.011.25%95.19%95.19%0.011.25%
610033200972775AHPLD CLN BURST 5/3.58LB/40LD56495840.0196.62%0.043.42%96.62%96.62%0.043.42%
333200032601AHSWS 6/55OZ1915020556.0792.66%0.1211.13%92.66%92.66%0.1211.13%
310033200030208AHSWS 12/55OZ66625633.6284.56%0.043.53%84.56%84.56%0.043.53%
310065333003309AHSWS 6/3KG12961327.7597.55%0.010.79%97.55%97.55%0.010.79%
610033200065279AHPLD AHOC FRESH SCENT 1/215LD BKT00 0 0.00%0.00%0.000.00%
610033200065132AHPLD AHOC FRSH SCNT 4/80LD COMPCTN IRC0  0 0.00%0.00%0.000.00%
610033200942211AHPLD AHOC ISLAND RAIN 3/8.87LB/115LD0  0 0.00%0.00%0.000.00%
PowerBI Totals  15941514063488.221168.8168.80%   
Totals  159415    80.00% 80.00% 1.0080.00%

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.