Filter Matrix on Percent Change

Question

Hello,I am trying to take only records that have a percent change where the percent change is above 50% or below -50%. It isn’t comparing year to date sales to sales year to date last year. The ask is to come up with a ProRata sales value. If I am running for April, it would be 4 for April / 12 * all of last year sales.Sales for all of last year = 15,000April’s ProRata sales = 5,000 (4/12) * 15,000I want to compare this year’s to date sales to the 5,000. If YTD sales is 3000, the percent cent to ProRata would be (3,000 – 5,000) / 5000 or 40%.And for that %, I only want the records that are above 50% or below -50%.Everything I do I get an out of memory processing error or circular dependency is detected.&nbsp;&nbsp;&nbsp;

parry2k · Answer

DataFunness&nbsp;It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.

anonymous · Answer

Hi All,Firstly&nbsp; parry2k&nbsp;thank you for your solution!And&nbsp;DataFunness,For your question, you are trying to get the percentage you want based on last year's total sales, and this year's sales data from when you started until today, judging by the month selected, right?For this question, here are some of our practical ideas we hope will help you!
ProRata_Sales_Percent_Change = 
VAR SelectedMonth = SELECTEDVALUE('Table'[Month])
VAR YearFraction = SelectedMonth / 12

-- Define last year's start and end dates
VAR LastYearStartDate = DATE(YEAR(TODAY()) - 1, 1, 1)
VAR LastYearEndDate = DATE(YEAR(TODAY()) - 1, 12, 31)

-- Calculate last year's total sales
VAR LastYearTotalSales = CALCULATE(
    SUM('Table'[Daily_Sales]),
    ALL('Table'),
    DATESBETWEEN('Table'[Date], LastYearStartDate, LastYearEndDate)
)

-- Define this year's start date and today's date
VAR CurrentYearStartDate = DATE(YEAR(TODAY()), 1, 1)
VAR CurrentDate = TODAY()

-- Calculate current year's year-to-date sales
VAR CurrentYearYTD = CALCULATE(
    SUM('Table'[Daily_Sales]),
    DATESBETWEEN('Table'[Date], CurrentYearStartDate, CurrentDate),
    REMOVEFILTERS('Table'[Month])
)

-- Calculate the ProRata percent change
RETURN
DIVIDE(CurrentYearYTD, LastYearTotalSales * YearFraction, 0)

Possibly our example data has limitations, there is no way to reproduce your negative number of cases, but the logic is unchanged, you can replace according to your actual situation, you want to filter their own data, you can filter the filter inside the filter, I hope that this idea will be helpful to you!
&nbsp;
Hope it helps!
Best regards,Community Support Team_ Tom Shen
If this post helps then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

datafunness · Answer

Anonymous, Thank you for the reply.&nbsp; The filter on the visiual (like you suggested) is how I had gotten the correct value (first image).&nbsp;&nbsp;&nbsp;&nbsp;The other half of my issue is I am trying to take that value and put it into this Summary table of different financial categories.&nbsp; The $42 MM is the Market Share value to be displayed and should not be the $60 MM.&nbsp; The problem is if I put all of the filters on the visual and not included in the measure, I get the $60 MM and not the correct filtered $ 42 MM.&nbsp; Does that make sense?&nbsp; I can get the report page that shows Market Summary correct (above image), the but this summary page is its own separate page.&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;DataFunness&nbsp;,We try to understand what you mean, but still can't understand exactly what you need, can you show the result you want, or provide some sample pbix, so that we can better help you solve the problem!Looking forward to your reply!

datafunness · Answer

Hi&nbsp;parry2k&nbsp;and&nbsp;Anonymous&nbsp;,&nbsp;The overall report has many pages.&nbsp; The Summary Page will display 1 measure from each page.&nbsp; In this pbix example, I need the “Growth Increase or Decrease” total at the bottom of the Existing Customers page to go to the Summary page.&nbsp; This is the one I’m having a problem with.&nbsp;&nbsp;The New Customer page is an example of how the total for New Customers is displayed on the Summary Page as expected.&nbsp;&nbsp;I created dummy data to match our data and part of our hierarchy. I may have switched between using&nbsp; the names customer and dealer, but they are one in the same.&nbsp; I added comments in the pages in the report.&nbsp; I attached a CSV of the export of the Existing Customers page that shows how I the expected rows to be included for Existing Customers.&nbsp; Please let me know what questions you have and thank you very much.&nbsp;Thank you so much&nbsp;Link to files:&nbsp;&nbsp;https://drive.google.com/drive/folders/13WGEsrVPOh9VqvIHZ-_Pwzlh3mu-M_5m?dmr=1&amp;ec=wgc-drive-globalnav-goto&nbsp;&nbsp;

Forum Discussion

Filter Matrix on Percent Change

7 Replies

Related content

Top N percent filter

Incorrect Total for Percent Change in Power BI Matrix Visual

Percent Change Between Matrix

Matrix table

monthly percent