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
ahmetturetmis
Frequent Visitor

Cumulative Total with multiple slicers

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: 

 
 

1.png

 

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

 

2.png

 

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:

 

ahmetturetmis_0-1593972230996.png

 

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

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

3 REPLIES 3
AllisonKennedy
Super User
Super User

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

 

Annotation 2020-07-06 185713.png

 

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.