topic Re: Calculate difference in Qty according to date and hierarchy in DAX Commands and Tips

Calculate difference in Qty according to date and hierarchy

Zuzu — Mon, 07 Sep 2020 02:08:16 GMT

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.

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

Re: Calculate difference in Qty according to date and hierarchy

Greg_Deckler — Sun, 06 Sep 2020 21:01:53 GMT

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-Roll/m-p/411443#M150

Re: Calculate difference in Qty according to date and hierarchy

Greg_Deckler — Sun, 06 Sep 2020 21:04:29 GMT

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.

Re: Calculate difference in Qty according to date and hierarchy

Zuzu — Sun, 06 Sep 2020 22:18:00 GMT

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.

Re: Calculate difference in Qty according to date and hierarchy

amitchandak — Mon, 07 Sep 2020 00:53:45 GMT

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/

Re: Calculate difference in Qty according to date and hierarchy

Zuzu — Mon, 07 Sep 2020 02:07:41 GMT

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

Re: Calculate difference in Qty according to date and hierarchy

Zuzu — Mon, 07 Sep 2020 03:10:11 GMT

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

Re: Calculate difference in Qty according to date and hierarchy

Crystal_YW — Tue, 08 Sep 2020 08:17:49 GMT

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