Forum Discussion

SinduN's avatar
SinduN
Icon for Helper I rankHelper I
7 years ago

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

 

Need Help

5 Replies

    • SinduN's avatar
      SinduN
      Icon for Helper I rankHelper I
      7 years ago

      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%
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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