cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zuzu
Frequent Visitor

Calculate difference in Qty according to date and hierarchy

Hello! I am new to Power BI and trying to wrap my head around different visualizations types and calculations for those. I am right now stuck with Hierarchy level calulations in a Matrix Viz.. If anyone can help me, that'd be great!! 

 

So I have a SQL table with Projects, Group, Unit, Code, Date, Quantity fields. I created a Hierarchy Project -> Group -> Unit -> Code. That is on my rows, Date is on Columns(I am right now only showing 2 weeks as columns- Prev Week, Current Week) and Quantity in Values. Everything works okay. The totals is shown correctly in each Hierarchy.

 

But now the request from Client is that they only want to see the "Type" where there is difference of Quantity values in the current and prev weeks. I am just flunking at it.. I don't know if I am going at it the right way or not. I tried to use ISINSCOPE() but could not get it right. 

I created a sample of the dataset in excel to show. This is the structure I haveThis is the structure I have

 

Any help is appreciated! Thank you.     I am still trying to figuure out how to add the .pbix file.. 

 

EDIT : This is the link to the sample pbix : https://www.dropbox.com/s/xdq2bwsxabx50rk/ChangeOverWeeks.pbix?dl=0 

 

 

7 REPLIES 7
amitchandak
Super User
Super User

@Zuzu ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. You can load to onedrive or dropbox and share.

In between refer one of the two can offer any help

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Zuzu
Frequent Visitor

Also @amitchandak  I went through the blogs you shared. The using ISFILTERED gives me the same values as that I have currently. The only thing I'm looking for is to eliminate(not show) the rows where the Hierarchy level(1,2,6,7 etc...) are the same for both the dates

@Zuzu write a measure,  when prensentation  is in the Unit level and the result for the week to compare is the same, say, set a fixed amount, like 1

 and use the conditional formating, when the measure is 1, the font is white, then you could realize what you want

Zuzu
Frequent Visitor

Hi @amitchandak   I created a sample pbix with dummy data but it has the same structure as my dataset. Let me know if you need it in a more detailed manner. Thank you for your help!

 

Link : https://www.dropbox.com/s/xdq2bwsxabx50rk/ChangeOverWeeks.pbix?dl=0

 

Greg_Deckler
Super User
Super User

@Zuzu - OK, found my difference between columns in a matrix formula. See Table (19) and Page 19 of attached PBIX. 

 

Note, you will probably not be able to attach a PBIX, you will need to share the PBIX via OneDrive or Box and share a link to it.

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi! Thank you for your quick reply! I tried recreating using what you suggested (Working67 file with Table19).. Unfortunately, that is not working in my case as it's getting the Max of the Code value in a hierarchy and the totals is also changing.. I don't know if I putting it the right way or not.. 

 

I have added the file in Dropbox. Link - https://www.dropbox.com/s/xdq2bwsxabx50rk/ChangeOverWeeks.pbix?dl=0

ChangeOverWeeks is what I have currently and Table19 is what I recreated using your example. 

 

Greg_Deckler
Super User
Super User

@Zuzu - Man, I just wrote a difference between two date columns in a matrix like just recently, let me see if I can find it. But, you will most definitely need MM3TR&R - https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors