Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sanimesa
Post Prodigy
Post Prodigy

Summarize / SUMX measure not working with filters

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. 

7 REPLIES 7
Anonymous
Not applicable

Hi,

 

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_Year])
&& HASONEVALUE ('v_Dim_Date'[Fiscal_Period_Number]),
CALCULATE (
AVERAGE(Fact_Employee_Turnover[Active_Count]),
FILTER (
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])
)
),
BLANK ()
)

@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. 

v-jiascu-msft
Employee
Employee

Hi @sanimesa,

 

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.

 

Best Regards,

Dale

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

Hello,

 

I have two tables like TBL and CUST. I have created the CUST table using the DAX.

CUST=
ADDCOLUMNS(
SUMMARIZE(FILTER(TBL,NOT(TBL[Account])=BLANK()),TBL[Account ],
"Sale",CALCULATE(SUM(TBL[Sale Amount]),FILTER(TBL,TBL[Ind]="Yes"),FILTER(TBL,TBL[Ind2]="Yes" && NOT(TBL[Code]) IN {"4"} ))
),
"Grp",
IF ( [Premium] >=0 && [Premium]<100, "0-100",IF ( [Premium] >=100&& [Premium]<500, "100-500",IF ( [Premium] >=500 && [Premium]<1000, "500-1000M",IF ( [Premium] >=1000, "1000+" )))))
Now, I have created a relationship between those two tables using "Account" column and created a Cust Grp column in TBL using Related(CUST[Grp]). I want build a customer count based on grouping. 
Problem:
Counts were fine when there were no filters applied but when I apply a filter on TBL tables Sale amount in CUST table is not filtering(I selected filtering as Both direction). As a result, counts in the group are not being calculated properly. 
For example, if I filter country in TBL to the "USA" the premium in the CUST table is not changing.
 
Please help me out!!!!
Thanks in advance!!!!!

@v-jiascu-msft 

 

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. 

 

Thanks.

Ashish_Mathur
Super User
Super User

Hi,

 

Share a sample data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Lind25
Resolver I
Resolver I

When using SUMX you will get duplicated results (ie it looks like the same value is being summed twice) if your sum by table contains duplicates in the current filter context.

This can be resolved by using a dimension table as the sum by table or by using the VALUES function to sum by the unique values in one column.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.