Table not showing all items but total is correct - variable measure

[Veles]

I've got a report page on financial data by site that users can slice and dice using a few slicers.

The matrix (new version) has Actual and Comparator columns - these are both measures - and a site heirachy on rows.

A slicer allows you to change the Comparator between difference scenarios (budget, prior year, etc.). The Actual meansure is not effected by this slicer because of an ALL in the DAX formula.

A slicer to select the month

A slicer to switch between showing month to date, quarter to date and year to date.

So Actual and Comparator are both variable based on the MTD/QTD/YTD slicer and the Comparator is also variable based on the Scenario slicer

On appearence this is working fine. However when I drill down to site level when looking at QTD or YTD, the only sites displayed are ones that have values in the Comparator column. There are sites with values in Actual that are not showing. However the total for the Actual column is correct - the column does not add down properly.

It also only shows sites with values in the selected Comparator in the selected month. E.g. if a site was closing so was only budgeted in month 1, if I do a YTD on month 2, it does not appear on the list.

Month to date works as intended. If a site has a value in Actual or Comparator, it appears on the list.

So the issue seems to be with my MTD and YTD formulas. Here's the code for the Actual measure. I've used the SWITCH TRUE method to switch between measures. First is MTD and is a very simple SUM (but with an ALL on Scenario to ignore the scenario slicer).

Second is YTD and third is QTD

Actual =
SWITCH (
    TRUE (),
    MIN ( 'Time Measures'[MsrRef] ) = 1, CALCULATE (
        SUM ( 'Combined Data'[Period Value] ),
        FILTER (
            ALL ( 'Combined Data'[Scenario] ),
            'Combined Data'[Scenario] = "Actual"
        )
    ),
    MIN ( 'Time Measures'[MsrRef] ) = 2, CALCULATE (
        SUM ( 'Combined Data'[Period Value] ),
        FILTER (
            ALL ( 'Combined Data'[Scenario] ),
            'Combined Data'[Scenario] = "Actual"
        ),
        FILTER (
            ALL ( 'Combined Data'[Period] ),
            'Combined Data'[Period] <= MAX ( ( 'Combined Data'[Period] ) )
        )
    ),
    MIN ( 'Time Measures'[MsrRef] ) = 3, CALCULATE (
        SUM ( 'Combined Data'[Period Value] ),
        FILTER (
            ALL ( 'Combined Data'[Scenario] ),
            'Combined Data'[Scenario] = "Actual"
        ),
        FILTER (
            ALL ( 'Combined Data'[Period] ),
            'Combined Data'[Period] <= MAX ( 'Combined Data'[Period] )
        ),
        FILTER (
            ALL ( 'Combined Data'[Quarter] ),
            'Combined Data'[Quarter] = MAX ( 'Combined Data'[Quarter] )
        )
    )
)

And here's the very similar measure for the Comparator measure. Only difference here is that I'm not using any filter on scenario and the user selected the scenario they want from the slicer.

Comparator = 
SWITCH (
    TRUE (),
    MIN ( 'Time Measures'[MsrRef] ) = 1, SUM ( 'Combined Data'[Period Value] ),
    MIN ( 'Time Measures'[MsrRef] ) = 2, CALCULATE (
        SUM ( 'Combined Data'[Period Value] ),
        FILTER (
            ALL ( 'Combined Data'[Period] ),
            'Combined Data'[Period] <= MAX ( ( 'Combined Data'[Period] ) )
        )
    ),
    MIN ( 'Time Measures'[MsrRef] ) = 3, CALCULATE (
        SUM ( 'Combined Data'[Period Value] ),
        FILTER (
            ALL ( 'Combined Data'[Period] ),
            'Combined Data'[Period] <= MAX ( 'Combined Data'[Period] )
        ),
        FILTER (
            ALL ( 'Combined Data'[Quarter] ),
            'Combined Data'[Quarter] = MAX ( 'Combined Data'[Quarter] )
        )
    )
)

[v-yulgu-msft]

Hi @Veles,

I was not able to replicate your scenario. Would you please provide some detailed sample data and show the image of your report design so that I can test. Or if possible, you could upload your pbix file.

Regards,
Yuliana Gu

[Veles]

Hi @v-yulgu-msft

I've loaded a sample version of my data (with a much simpler model) that is replicating the issue.

If you click on YTD or QTD on the slicer, some sites will disappear, but the total will be correct.

https://1drv.ms/u/s!Ag3GCfmr8Sa4g3NC3fsxJsFxh9DS

[Veles]

Has anyone managed to figure this one out?

I've been attempting to fix the problem (basically by sticking ALLs in front of things) and had no luck.

Not sure if this is a problem with the visual itself (as the data is there) or my formulas.

Thanks

[Anonymous]

@Veles

I have a different method for you, which I think you should try out.

It goes like this:

Make a table called Measures with a column for the measure name and a column for a sortorder similar to your table called Time measures.

You then make measure in your combined data table:

VAR MTD = [Some measure for MTD]
VAR QTD = [Some measure for QTD]
VAR YTD = [Some measure for YTD]

VAR SelectMeasure = MIN ( 'Measures'[SortOrder] )

RETURN
IF (
HASONEVALUE ( 'Measures'[SortOrder] );

SWITCH ( SelectMeasure;
1; MTD;
2; QTD;
3; YTD;

6 )
)

However, this approach will only work with single select, so be aware of that.

[Veles]

Hi @Anonymous,

I can't get the VAR SelectMeasure formula to work. It tells me that the syntax for RETURN is incorrect

EDIT: I have it working now but unfortunately the matrix still hides data in my Actual column when using QTD or YTD it it doesn't exist in the Scenario I'm selecting for the Comparator 2 column.

Comparator 2 = 
VAR MonthTD = SUM ( 'Combined Data'[Period Value] )
VAR YearTD = CALCULATE (
        SUM ( 'Combined Data'[Period Value] ),
        FILTER (
            ALL ( 'Combined Data'[Period] ),
            'Combined Data'[Period] <= MAX ( ( 'Combined Data'[Period] ) )
        )
    )
VAR QuarterTD = CALCULATE (
        SUM ( 'Combined Data'[Period Value] ),
        FILTER (
            ALL ( 'Combined Data'[Period] ),
            'Combined Data'[Period] <= MAX ( 'Combined Data'[Period] )
        ),
        FILTER (
            ALL ( 'Combined Data'[Quarter] ),
            'Combined Data'[Quarter] = MAX ( 'Combined Data'[Quarter] )
        )
    )

 VAR SelectMeasure = MIN('Measures 1'[Sort])
RETURN IF(HASONEVALUE('Measures 1'[Sort]),
	
SWITCH ( SelectMeasure,
1, MonthTD,
2, QuarterTD,
3, YearTD,

6)
)

[Anonymous]

@Veles Did you try out my solution?

[Veles]

Thanks for that @Anonymous

My laptop died that day unfortunately so not managed to look at it yet as I need to wait for IT to install desktop onto the spare laptop. 😞

[Anonymous]

@Veles

Will take a look at your pbix file later on today and see if I can do anything.

[Anonymous]

Hi @Veles

Could you post the results from your measures in table format? And also are you aware of the YTD, QTD and MTD functions in DAX in Power BI?

[Veles]

Hi @Anonymous

My period is not actually in month format (P01, P02,...,P12) so the YTD, QTD, MTD quick measures don't work.

Table looks like this:

Region	Sub-Region	Name	Actual	Comparator
Region	Sub-Region	Site 1	-133936	-113399
Region	Sub-Region	Site 2	4913	8414
Region	Sub-Region	Site 3	176411	114427
			Total	170186	9442

But should look like this:

Region	Sub-Region	Name	Actual	Budget
Region	Sub-Region	Site 1	-133936	-113399
Region	Sub-Region	Site 4	-387	
Region	Sub-Region	Site 5	-52	
Region	Sub-Region	Site 6	-13	
Region	Sub-Region	Site 7	14	
Region	Sub-Region	Site 8	168	
Region	Sub-Region	Site 9	3751	
Region	Sub-Region	Site 2	4913	8414
Region	Sub-Region	Site 10	5453	
Region	Sub-Region	Site 11	7192	
Region	Sub-Region	Site 12	12008	
Region	Sub-Region	Site 13	12603	
Region	Sub-Region	Site 14	82061	
Region	Sub-Region	Site 3	176411	114427
		Total	170186	9442

Even though the totals aren't being affected by my scenario slicer, the individual lines that are pulling through are.

If I take out the Comparator measure and Scenario slicer and just use a measure for Budget that is constructed in the same way as Actual (just flitereing on the Budget scenario instead) the table works perfectly fine.