Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
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.
Best regards,
Yuliana Gu
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:
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.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |