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.
Hello All,
I'd like to show the SUM of the total sales lost by customers who are performing worse than last year. I have a YoY calculated measure, that I thought you could filter on, however it seems that you cannot have a page filter on a calculated measure.
This has got to be a simple solution that I am missing.
Any help would be greatly appreciated!
Solved! Go to Solution.
Hello @Anonymous
A couple of options I can think of:
Sales Lost = SUMX ( VALUES ( Sales[Customer] ), VAR CustomerYoY = [Existing YoY Measure] RETURN IF ( CustomerYoY < 0, CustomerYoY ) // You could change the last argument to ABS ( CustomerYoY ) to view the result as a positive value )
Regards,
Owen
Hi,
Share some data and show the expected result.
Hello @Anonymous
A couple of options I can think of:
Sales Lost = SUMX ( VALUES ( Sales[Customer] ), VAR CustomerYoY = [Existing YoY Measure] RETURN IF ( CustomerYoY < 0, CustomerYoY ) // You could change the last argument to ABS ( CustomerYoY ) to view the result as a positive value )
Regards,
Owen
This helped. However Im still not able to see the correct total. I tried to use this technique, but total shows only for the sales that has current year line.
SalesLost = SUMX ( VALUES ( Sales[Customer] ),SUMX(VALUES(Sales[Item Name]), VAR CustomerYoY = IF(OR(ISBLANK([Last Period Revenue]),[Last Period Revenue]<=0), [Prior Year Revenue],BLANK()) RETURN IF ( CustomerYoY < 0, CustomerYoY ) ))
Here is the concept.
All im tyring to do is show the lost customer sales, who didnt purchase for 6 months from selected month.
I created 3 measures, All works except the total. It seems to consider total onlly for current year or not at all.
Last period sales =
CALCULATE( SUM(Sales[REVENUE_USD_FxAdj]),DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-7,MONTH) )
prior year sales =
CALCULATE ( -Sales[SALES REVENUE_PY_FxAdj], DATEADD ( 'Calendar'[Date], -1, YEAR ) )
lost customer sales = If las period sales is less than or equal to 0 or blank, return the prior year sales for that month in negative.
IF(OR(ISBLANK([Last Period Sales]),[Last Period Sales]<=0), [Prior Year Sales],BLANK())
I am seeing right values for the lost customer sales but total is blank.
I tried all possible SUMX, SUMMARIZE, still it doesnt work.
At the end I want to be able to see the total for each month in the below table
Please can you help, I am probably missing something @OwenAuger Please can you help
Hi @EV
It looks like the issue is due to Customers/Items that are not present in the current filtered period being omitted from the SUMX/VALUES iteration.
It appears you don't have dimension tables for Customer/Item.
One solution based on your current data model would be to write SalesLost something like this.
The main change is to ensure that the Customer/items values iterated over are those that appeared in prior year, which are the only ones you need to consider for the SalesLost measure. These values are stored in the CustomerItemPriorYear variable.
SalesLost = VAR CustomerItemPriorYear = CALCULATETABLE ( SUMMARIZE ( Sales, Sales[Customer], Sales[Item Name] ), DATEADD ( 'Calendar'[Date], -1, YEAR ) ) RETURN SUMX ( CustomerItemPriorYear, VAR LastPeriodRevenue = [Last Period Revenue] VAR CustomerYoY = IF ( OR ( ISBLANK ( LastPeriodRevenue ), LastPeriodRevenue <= 0 ), [Prior Year Revenue], BLANK () ) RETURN IF ( CustomerYoY < 0, CustomerYoY ) )
Another solution would be create dimension tables for both Customer and Item and iterate over those, as they would naturally contain all Customer/Item values regardless of whether present in Sales.
Regards,
Owen
Thank you very much for your time @OwenAuger
I do have customer and Item dimension, When i used the below DAX, the report keeps failing with memory error, but works in small dataset.
SUMX(SUMMARIZE('Parent','Parent'[PARENT_NAME]),SUMX(SUMMARIZE('Item','Item'[ITEM_DESC]),IF([Last Period Revenue]<=0 || [Last Period Revenue] =BLANK(),[Prior Year Revenue],0)))
the DAX you have given here worked for me, however there are some values missing out, making the total incorrect, for each month.
I am sharing the PBI file with sample data in message, please could you check?
thank you very much in advance.
Thank you Owen! This got me on the right track. I appreciate the help!
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |