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

Diffence Report

Greetings,

Any help with this problem will be greatly appreciated. Trying to create a difference report and I am running into problems with displaying two summaries (Net Increase and Decreases). The report is intended to allow the user to select beginning and end dates from the dropdowns and to display summary and detail information (see screen shot). The problem is that I cannot seem to get the right filter context needed to display Increase and Decrease – the net change is displaying fine. Again, thanks in advance for any suggestions.

 

Below: Screen shot of expected report, and link to Power BI application (Google Drive)

Expected Results.JPGDifference Report.pbix

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Diffence Report

@aww91,

Create the following measures in your Demand by Dates table.

checkpositive = IF([SumEndDate]>[SumBegDate],[SumEndDate]-[SumBegDate],0)

checknegative = IF([SumEndDate]<[SumBegDate],[SumEndDate]-[SumBegDate],0)

Increase = IF(COUNTROWS(VALUES('Demand By Dates'[PPM ID]))=1, [checkpositive],SUMX(VALUES('Demand By Dates'[PPM ID]),[checkpositive]))

Decrease = IF(COUNTROWS(VALUES('Demand By Dates'[PPM ID]))=1, [checknegative],SUMX(VALUES('Demand By Dates'[PPM ID]),[checknegative]))

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User
Super User

Re: Diffence Report

If you are referring to your totals in your table, then see this article:

 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


aww91 Frequent Visitor
Frequent Visitor

Re: Diffence Report

Good article, very well written however my situation is a little different. I am trying to sum separately positive and negative in the measure SumNetChg.

aww91 Frequent Visitor
Frequent Visitor

Re: Diffence Report

Any other ideas or suggestions? I have been working on this problem for a week. Thanks in advance.

Moderator v-yuezhe-msft
Moderator

Re: Diffence Report

@aww91,

Create the following measures in your Demand by Dates table.

checkpositive = IF([SumEndDate]>[SumBegDate],[SumEndDate]-[SumBegDate],0)

checknegative = IF([SumEndDate]<[SumBegDate],[SumEndDate]-[SumBegDate],0)

Increase = IF(COUNTROWS(VALUES('Demand By Dates'[PPM ID]))=1, [checkpositive],SUMX(VALUES('Demand By Dates'[PPM ID]),[checkpositive]))

Decrease = IF(COUNTROWS(VALUES('Demand By Dates'[PPM ID]))=1, [checknegative],SUMX(VALUES('Demand By Dates'[PPM ID]),[checknegative]))

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
aww91 Frequent Visitor
Frequent Visitor

Re: Diffence Report

Outstanding! Works perfectly. Thank You.