Running Total Part 2

[Lucy64]

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: 

[v-sihou-msft · Accepted solution]

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

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? 

[Lucy64]

Thanks Simon, that did the trick!