Forum Discussion

Lucy64's avatar
Lucy64
Advocate II
9 years ago
Solved

Running Total Part 2

Last year I got help here on building the following formula:

 

RunningTotal = MAXX(vwScalesDataFabricationNetValue, SUMX(
FILTER(
SUMMARIZE(CALCULATETABLE(vwScalesDataFabricationNetValue, ALLEXCEPT(vwScalesDataFabricationNetValue, vwScalesDataFabricationNetValue[BuildingName]), ALLSELECTED(vwScalesDataFabricationNetValue[BuildingName])), vwScalesDataFabricationNetValue[MachineName], "NetVal", SUM(vwScalesDataFabricationNetValue[Fabrication Net Value])),
[NetVal] >=
SUMX(FILTER(CALCULATETABLE(vwScalesDataFabricationNetValue, ALLEXCEPT(vwScalesDataFabricationNetValue, vwScalesDataFabricationNetValue[BuildingName]), ALLSELECTED(vwScalesDataFabricationNetValue[BuildingName])), vwScalesDataFabricationNetValue[MachineName]= EARLIER(vwScalesDataFabricationNetValue[MachineName], 2)), vwScalesDataFabricationNetValue[Fabrication Net Value])), [NetVal]))

 

However changes have been made and I have run into to problem.  As you can see the formula which is used for the line in this chart is working well when I filter on Building Name.  

 

However on the 3rd diagram when I filter on week number the result of the calculated runnning total is not the same.

 

Would appreciate your help to resolve this, if needed you can find the file here: 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Lucy64

     

    In your scenario, your running total calculation is over all week numbers. When you select a week number in slicer, it just filter each fact data for your selected week number. If you want to show the correct running total pct, you also need to have your calculation group on "Week Number". Just add the "Week Number" column in ALLEXCEPT() function.

     

    RunningTotal =
    MAXX (
        vwScalesDataFabricationNetValue,
        SUMX (
            FILTER (
                SUMMARIZE (
                    CALCULATETABLE (
                        vwScalesDataFabricationNetValue,
                        ALLEXCEPT (
                            vwScalesDataFabricationNetValue,
                            vwScalesDataFabricationNetValue[BuildingName],
                            vwScalesDataFabricationNetValue[WeekNumber]
                        ),
                        ALLSELECTED ( vwScalesDataFabricationNetValue[BuildingName] )
                    ),
                    vwScalesDataFabricationNetValue[MachineName],
                    "NetVal", SUM ( vwScalesDataFabricationNetValue[Fabrication Net Value] )
                ),
                [NetVal]
                    >= SUMX (
                        FILTER (
                            CALCULATETABLE (
                                vwScalesDataFabricationNetValue,
                                ALLEXCEPT (
                                    vwScalesDataFabricationNetValue,
                                    vwScalesDataFabricationNetValue[BuildingName],
                                    vwScalesDataFabricationNetValue[WeekNumber]
                                ),
                                ALLSELECTED ( vwScalesDataFabricationNetValue[BuildingName] )
                            ),
                            vwScalesDataFabricationNetValue[MachineName]
                                = EARLIER ( vwScalesDataFabricationNetValue[MachineName], 2 )
                        ),
                        vwScalesDataFabricationNetValue[Fabrication Net Value]
                    )
            ),
            [NetVal]
        )
    )

     

     

    Regards,

     

3 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    Lucy64

     

    In your scenario, your running total calculation is over all week numbers. When you select a week number in slicer, it just filter each fact data for your selected week number. If you want to show the correct running total pct, you also need to have your calculation group on "Week Number". Just add the "Week Number" column in ALLEXCEPT() function.

     

    RunningTotal =
    MAXX (
        vwScalesDataFabricationNetValue,
        SUMX (
            FILTER (
                SUMMARIZE (
                    CALCULATETABLE (
                        vwScalesDataFabricationNetValue,
                        ALLEXCEPT (
                            vwScalesDataFabricationNetValue,
                            vwScalesDataFabricationNetValue[BuildingName],
                            vwScalesDataFabricationNetValue[WeekNumber]
                        ),
                        ALLSELECTED ( vwScalesDataFabricationNetValue[BuildingName] )
                    ),
                    vwScalesDataFabricationNetValue[MachineName],
                    "NetVal", SUM ( vwScalesDataFabricationNetValue[Fabrication Net Value] )
                ),
                [NetVal]
                    >= SUMX (
                        FILTER (
                            CALCULATETABLE (
                                vwScalesDataFabricationNetValue,
                                ALLEXCEPT (
                                    vwScalesDataFabricationNetValue,
                                    vwScalesDataFabricationNetValue[BuildingName],
                                    vwScalesDataFabricationNetValue[WeekNumber]
                                ),
                                ALLSELECTED ( vwScalesDataFabricationNetValue[BuildingName] )
                            ),
                            vwScalesDataFabricationNetValue[MachineName]
                                = EARLIER ( vwScalesDataFabricationNetValue[MachineName], 2 )
                        ),
                        vwScalesDataFabricationNetValue[Fabrication Net Value]
                    )
            ),
            [NetVal]
        )
    )

     

     

    Regards,

     

    • Lucy64's avatar
      Lucy64
      Advocate II
      9 years ago

      Thanks Simon, that did the trick!

    • Lucy64's avatar
      Lucy64
      Advocate II
      9 years ago

      One more question, I hope you can help.  This works when only one building is selected however it does not if I select more than 1 building on the hierarchy slicer.  

       

      The Hierarchy is:  

      BuildingName

      ProductionLineName

      MachineName

       

      The Machine Name is used on the graph.

       

      Any suggestions?