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
kjohn10
Helper I
Helper I

Calculate sum using measure as filter results in blank data in matrix

I have a lot of measures calculating values for my table, and I use those measures to generate further measures. I'm at a point where I simply want to sum a sales column based on one of those later measures. It seems to work OK with one filter value, but not others. I've found there is 'missing' data for many 'Products' in any given month, and I read that the 'Matrix' visual will fail to provide values if this occurs.

As a test, I went into the database and provided 0 (zero) values for sales in months that were missing before. This however didnt solve the problem. So now I'm guessing my measure filter is not quite right, even though other similar measures work.

Here is what the matrix looks like with the following measures:

salesunits.png

Seg_AX_SalesUnitsSum = SUMX( FILTER( ALLSELECTED('Stage NetSales'[Product]), [Segments] = "AX"), CALCULATE(SUM('Stage NetSales'[SalesUnits])))

Seg_AZ_SalesUnitsSum = SUMX( FILTER( ALLSELECTED('Stage NetSales'[Product]), [Segments] = "AZ"), CALCULATE(SUM('Stage NetSales'[SalesUnits])))

Seg_AZ_SalesUnitsSum2 = SUMX( FILTER( ALLSELECTED('Stage NetSales'[SalesUnits]), [Segments] = "AZ"), CALCULATE(SUM('Stage NetSales'[SalesUnits])))

For Seg_AZ_SalesUnitsSum, the total is correct (9576), and that entire sales value is in Period 2017-09.

Then in the lower matrix, using Seg_AZ_SalesUnitsSum2, you'll note all I changed was the 'ALLSELECTED' to use [SalesUnits] intead, and now the 0 values show up for all periods, but the 9576 for 2017-09 is not there, and the total is 0.

 

Is it something simple I'm missing with my FILTER implementation? The [Segments] measure is calculated based on other measures. I can try and put together some sample data to help nail down the issue, but its quite a complex mix of measures and significant data to do so. Unfortunately I cannot share my source...sales data and all Smiley Frustrated

 

3 REPLIES 3
v-yulgu-msft
Employee
Employee

Hi @kjohn10,

 

Your measures are all correct. Please see below screenshots to check the reason for blank results in Matrix visual.

 

In your scenario, you combined [SegABC] and [SegXYZ] as a new measure which is used to filter data records. However, take [Seg_CY_SalesUnitsSum] as an example, even there is corresponding records for [SegXYZ]="Y" per month, there is no correspond rows for [SegABC]="C" in any month. When you were trying to filter data with syntax [Segs]="CY", you were actually filtering data with syntax [SegABC]="C"&& [SegXYZ]="Y" in a particular month, you got none result. 

 

3.PNG1.PNG2.PNG

 

Best regards,

Yuliana Gu

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

OK, I see what happens, thanks for breaking that out. What I dont want is to have the period filter the sales units back out by period after the 'Segs' has been determined for the given 'Sales Date Range' filter. I want to set that Sales Date Range (last 3 months, or last 6 or 12 months) and get the products into a Seg based on those first 2 table calcs (%SGM & Running Total for ABC, and CoefVar for XYZ). That is shown in the final smaller table:

segs.png

 

Now, I want to keep the Products in those Segs, and perform calcs over the last 3,6,12 months, by month, again keeping the Products in the Segs determined. Is it possible to keep them bucketed and do more calcs? Or will adding 'Period' filters always 'break' that previous measures calcs? I cant think of a way to keep them together.

kjohn10
Helper I
Helper I

I figured this would be too difficult to try and answer without an example; here is a subset of data with all the same measures I established. Hopefully this will help find the problem/solution! 

There are some 'gaps' in the data, note no products fit in 'AY' and 'AZ' seg, but the 'CY' and 'CZ' show the problem I'm having.

pbix here.

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.