Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear Community,
I have a scenario where i have department with multiple codes. Each department has 3 sub departments with each sub departments having codes on different months. I am using matrix visual to show the count of Codes in each Department per month. I could get that in matrix. But when using the sub department in visual level filter, the total also gets filtered. I need to the get the overall / original total for each sub department even if the visual level filter is applied. I have tried using All, AllSelected & All except in measures.
Please help me to get the overall total in using measures as my source is live connection.
Attached the image for reference & find the link below for the sample PBIX file i have used to the screen shot.
PBIX download Link : https://filebin.net/uzhobbcdsec2zrh2
(has Both PBIX & the Sample Excel)
Thanks In Advance.
Note: Below Expected result is what i need to get after filtering Sub Depart into Visual Level Fitler but with Overall totalPBIX & Source
Solved! Go to Solution.
@Anonymous hey add following two measures and then use Code Count in your matrix
Total Count = COUNT( Child[Code] ) Code Count = IF ( ISINSCOPE( Child[Depart]), [Total Count], CALCULATE( [Total Count], ALL(Child[Depart]) ) ) * DIVIDE( [Total Count],[Total Count])
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.
Dear members,
This accepted solution works best for the scenario i have mentioned in the screenshot(which is import mode).
I solved my acutal issue where my source is Live Connection ( SSAS Cube) and the "ISINSCope" function is not supported yet.
To get the current context i used "MAXA" instead of "ISINSCOPE" to calculate the count. Posting this solution for live connection for any members reference. Thanks all. @parry2k , @Cmcmahan @tex628
IF( MAXA(Parent[Depart]), Total Count] ,CALCULATE( [Total Count], ALL(Child[Depart]) )
So I went in to play with your .pbix file, and figured out a way to do this without getting crazy and checking context to calculate differently based on whether you're in a parent or child row of the matrix.
Originally, you had ALLEXCEPT('Child', Child[Month]) which counted a table of ALL children, EXCEPT keeping current month filters. This gave results that were counting children from all departments, though clearly we want results that still respect the current department filters.
I tried using ALLEXCEPT('Parent','Parent'[Department]), but that gave the same result in each row, since getting ALL of the Parent table EXCEPT keeping filters on Department, would clearly combine all the months.
So I combined them! I changed your ALLEXCEPT measure to this:
Count overall_allexcept = CALCULATE(COUNT('Parent'[Code]),ALLEXCEPT('Parent', 'Child'[Month], 'Parent'[Depart]))
Which gave me the results you showed in your expected results section. You're getting ALL rows of Parent, except you keep filters on Month (so that the child results are correct. The parent Depart row doesn't have any month filters, so it counts everything) and filters on Depart (so that you're not adding in values from other departments for each month subtotal)
If you have further questions, feel free to post here @Anonymous
hi @Cmcmahan ,
Thanks for this solution, this works well with the kind of scenario in the pbix file. But since due to many different tables joined in the cube and with date in different table im still getting wrong count. Have dropped a brief explanation of the actual scenrio to you. If possible please let me know about the same.
Once again thanks for taking time for getting a solution for the issue.
Thanks & Regards,
Praveen
@Anonymous hey add following two measures and then use Code Count in your matrix
Total Count = COUNT( Child[Code] ) Code Count = IF ( ISINSCOPE( Child[Depart]), [Total Count], CALCULATE( [Total Count], ALL(Child[Depart]) ) ) * DIVIDE( [Total Count],[Total Count])
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.
Oh man, ISINSCOPE is amazing. It would have solved so many problems I've had with matrices. Thank you for pointing it out!
Hi @parry2k ,
Thank you for the solution.
It works perfectly in ths sample code i have provided. But my actual scneraio has a cube source and ISINScope is not available for Live connection sources. Is it possible to get a solution for a live connection sources please.
Kindly let me know about this.
Thanks & Regards,
Praveen
Can you do a
IF( Selectedvalue('Departments'[Department]) = BLANK() , Calculate([Total count] , all('Departments') , [Total count])
Department being the column dimension that your using.
Hi @tex628 ,
Thanks for your reply. PBI is not allowing to use a calcualte column inside a IF condition. Please find the image for reference. Glad you could try for an solution. Kindly do let me know if there are any workaround for the same.
Thanks &Regards,Praveen
The example i gave will not give you the result that you are looking for, sorry! Take a look at my previous reply and see if that can be applied.
In regards to the image below, I forgot to add a ")" after the all('Parent') statement, thats why it's not working!
Hi @tex628 ,
I tried including the close bracket ")" and im getting the row level fitlered count only. As my source is Live connection, im not able to alter the data. Thanks for your suggestion and help. Will try out an solution for this.
Thanks alot.
@tex628
The problem with that solution is that you still have a SELECTEDVALUE if there's only one record in the subgroup. So it would work in most cases, but still give you the wrong result when there's only one value.
Honestly, these workarounds for matrices are ridiculous, and I encourage everybody to vote on this idea to allow custom totals/subtotals: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17401381-conditional-formatting-f...
Heheh you are totally correct!
I had a similar issue a while back, which i solved by adding blank rows to the table.
Essentially what i did was:
A1 | A1 |
A1 | BLANK() |
By doing this to every record and then using one column for the slicer and one for the matrix i could add a blank row to every record and through that always use selectvalue to identify the total.
Im not sure if it can be applied to your model but its worth a try maybe 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |