Need help with fixing a measure - when this measure is added to a table, the filters do not seem to work.
Table: Regular sales table with Company, Customer Group, Item, Sale Amount, Invoice date etc.
Total YTD Price Traction= SUMX(SUMMARIZE(Invoices, Invoices[Company], Invoices[CustomerGroup], Invoices[ItemCode], "YTD Price Traction", [Price Traction YTD]), [YTD Price Traction])
When this measure is not added to a table containing columns from the table, slicers on columns Company and CustomerGroup work as expected. But when The above measure is added, the slicers simply do not filter the table properly. It looks like the measure is ignoring the slicers on the page and in some cases returning multiple rows.
Did you find the fix for this ? I have same issue and looking for a solution.
My case: I have an Average measure with MTD filters in it and then trying to use that in sumx measure for SUM of grandtotal / aggregation. However new sumx measure is not considering filters in Average measure.
This is my sumx measure syntax:
AVGSUMMeasure = SUMX(values(Fact_Employee_Turnover[Active_Count]),[MTD_ActiveCount])
This is my MTD Average measure syntax:
MTD_ActiveCount = IF (
&& HASONEVALUE ('v_Dim_Date'[Fiscal_Period_Number]),
ALL ( 'v_DIM_date' ),
'v_DIM_date'[Fiscal_Year] = VALUES ( 'v_DIM_date'[Fiscal_Year] )
&&'v_Dim_Date'[Fiscal_Period_Number] = VALUES ( 'v_Dim_Date'[Fiscal_Period_Number] )
&& 'v_DIM_date'[Actual_Date] <= MAX ( 'v_DIM_date'[Actual_Date] ) && v_Dim_Date[Actual_Date] >=VALUES( v_Dim_Date[Fiscal_Period_StartDate])
@columbustechi I did not find a solution to the specific issue.
However, we do the custom fiscal period MTD values slightly differently. An example:
MTD Sales =
VAR current_month_tab = FILTER('Calendar 445', [Year] = YEAR(TODAY()) && [Period No] = [(Current Period)])
RETURN CALCULATE([Total Sales], current_month_tab)
The Current Period measure:
(Current Period) = LOOKUPVALUE('Calendar 445'[Period No], [Date], TODAY())
Maybe this approach will simplify things for you.
What's the formula of [Price Impact YTD]? It would be helpful to troubleshoot if you can share a sample. Please mask the private data first.
I have two tables like TBL and CUST. I have created the CUST table using the DAX.
The [Price Traction YTD] is as follows:
Price Traction YTD =
VAR ytd_qty = [YTD Quantity]
VAR ytd_avg_price = [YTD Average Unit Price]
VAR baseline_avg_price = [Baseline Average Price]
VAR avg_list_price = [Average List Price]
VAR avg_price = IF(baseline_avg_price=BLANK(), avg_list_price, baseline_avg_price)
RETURN ytd_qty*(ytd_avg_price - avg_price)
Basically, here is my problem at a high level - I added several measures to a table, most of them to do with average prices and quantities across different periods. However, the totals of the table does not match the total of individual rows (because individual rows are grouped by customer,item etc., and the totals are getting averaged all across). I am trying to find the best design pattern to avoid this.
Check out the News & Announcements to learn more.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
Mark your calendars and join us for our next Power BI Dev Camp!
Check out our new Discover Your Career Path blog post series and get all the details.