Cumulative Total with multiple slicers

[ahmetturetmis]

Hello everyone, 

This is my first post. I've started using Power Bi in a construction company recently. I would like to sum some quantities and obtain cumulative sums for different slicer and time period combinations like selection Project A, Activity X, between 2019 etc. 

However when all of the slicers are off both shows the correct graph like this: 

But when I choose a specific project green cumulative line stay same but red one changes relative to the selected slicer. 

While this is working :

Planned Mh **bleep** =
CALCULATE (
    SUM ( PlannedMh[Pln MH] ),
    FILTER (
        ALLEXCEPT (
            PlannedMh,
            PlannedMh[AreaCODE],
            PlannedMh[Cost Code],
            PlannedMh[Project CODE]
        ),
        MAX ( PlannedMh[DATE] ) >= PlannedMh[DATE]
    )
)

But this one is not working:

 Actual Mh **bleep** =
CALCULATE (
    SUM ( ActualMh[Act MH] ),
    FILTER (
        ALLEXCEPT (
            ActualMh,
            ActualMh[AreaId],
            ActualMh[Foreman Name],
            ActualMh[LocationId],
            ActualMh[ProjectId],
            ActualMh[CBSId],
            ActualMh[Worker Name]
        ),
        MAX ( ActualMh[Date] ) >= ActualMh[Date]
    )
)

We had a data model like this:

I would be thankful for you, if you know some solution to this problem.

[AllisonKennedy · Accepted solution]

It looks like you're using ALLEXCEPT to ignore all filters and slicers, so if you are trying to slice by anything other than what is in that ALLEXCEPT part of the formula, it won't change the green line.

To get both lines to update with the same slicers, you'll need to create slicers from the Lookup or Type tables (the 4 at the top of your data model image).

Also, I question why the Cross Filter Direction is set to 'Both' for the Type to Lookup Table relationships.

[parry2k]

@ahmetturetmis regardless of the specific issue, you mentioned in this post (which is probably solved by the solution from @AllisonKennedy ) but I'm replying in context to overview of your model:

- first and foremost, you have pretty much most of the relationship set to cross filter direction to both and it is something not highly recommended and can lead to big performance impact and also can lead to some unexpected results. You need to first fix that.

- 2nd seems like you are using dates in your visuals/calculations. As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

If you already have a date dimension, just ignore this but point is, use date dimension in your calculations/visuals where you working with time.

- 3rd, strive for star schema, means, you should have common dimension  tables to filter your various facts tables, read more here

. 

Someone might end up providing you a solution to solve your immediate problem, but if you focus on a more robust/best practice model, it will take you a long way. Sorry, it is a bit off-topic but it is important. Good luck!

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[ahmetturetmis]

@parry2k  thanks to your nice feedbacks. I'm recently work for a construction company and my main work is Power BI now which I don't have any previous experience. I'm trying to improve the report step by step. 

-Actually I used the two way due to the syncronazing the filters, for example when I select project A, I need to see specific Activities from the Project A not the rest. And I have 4 fact tables 2 of them are smilar but the planned ones don't have some dimentions like area or location which ActualMh and actualQty have. So I couldn't merge them correctly. 

-This is not the whole model I have a calendar table too.

-I would like to make this model more star schema but as I said before my fact tables have different amount dimentions and when I merge them there are a lot of nulls. And I don't know how to deal with different dimention fact table merges.

For example, now I have a problem to see the both Foreman Name, ActualMh and ActualQty in a same table but I have problems due to the there are two Foreman Name columns in total and I don't know how to filter them correctly. I get some repetitive amounts like that.