Conditional columns in SUMMARIZE...

[Matty]

Hi Team,

I have a use case at the moment where I need to create a new table (inside a measure) that considers a parameter set by the user.

I know I can do the following:

VAR Scenario A =
    SUMMARIZE (
        Table1,
        Table1[Shop],
        Table1[Product],
        Table1[Week]
    )

VAR Scenario B =
    SUMMARIZE (
        Table1,
        Table1[Shop],
        Table1[Category],
        Table1[Week]
    )

But what I'm wondering is whether it's possible to do something like this:

VAR Scenario =
    SUMMARIZE (
        Table1,
        Table1[Shop],
        IF ( SELECTEDVALUE ( Table2[Selection] ) = "Product", Table1[Product], Table1[Category] )
        Table1[Week]
    )

Which isn't syntactically valid in its current form, but hopefully illustrates what I would like to be able to do, i.e. to decide what is or isn't included within SUMMARIZE.

Hoping for some guidance, even if it's just to confirm that this kind of approach isn't possible.

Cheers,

Matty

 

[Matt Allington]

Why are you using summarize inside a measure? It can be done, but I’ve seen more unnecessary use than necessary use.

you can’t do it the way you have shown. if you’re using PBI, you could investigate if the new field parameters feature will do this.

 

[Matty]

Why are you using summarize inside a measure? It can be done, but I’ve seen more unnecessary use than necessary use.

you can’t do it the way you have shown. if you’re using PBI, you could investigate if the new field parameters feature will do this.

Hi Matt,

Thanks for responding. And yes, I'm using PBI.

Here's the measure SUMMARIZE is part of:

ABS Difference =
VAR Choice =
    SELECTEDVALUE ( 'Table2'[Choice] )
VAR WithProduct =
    SUMMARIZE (
        Table1,
        Table1[Shop],
        Table1[Product],
        Table1[Week]
    )
VAR WithCategory =
    SUMMARIZE (
        Table1,
        Table1[Shop],
        Table1[Category],
        Table1[Week]
    )
VAR Result =
    IF (
        Choice = "Product",
        SUMX (
            WithProduct,
            CALCULATE (
                ABS (
                    SUM ( Table1[Forecast] )
                        - SUM ( Table1[Orders] )
                )
            )
        ),
        SUMX (
            WithCategory,
            CALCULATE (
                ABS (
                    SUM ( Table1[Forecast] )
                        - SUM ( Table1[Orders] )
                )
            )
        )
    )
RETURN
    Result

So, the measure takes the choice taken by the user and calculates the absolute difference based on the chosen grouping. The measure works well, but I'm just wondering whether there's a better way to write it.

Cheers,

Matty

 

[Matt Allington]

can you please post an image of the data model, and also post an image of a matrix/table showing some results. It is not possible to effectively write DAX without these things

 

[Matty]

Hi Matt,

The data model is very simple...

Table1 looks as follows:

Shop	Category	Product	Week	Forecast	Orders
100	01	A	1​	100	55
100	01	A	2​	150	135
100	01	A	3​	100	110
100	01	B	1​	50	75
100	01	B	2​	50	10
100	01	A	1​	50	10
100	01	A	2​	25	65
100	01	B	1​	60	45
100	01	B	2​	80	85
100	01	B	3​	220	375
100	02	C	1​	45	35
100	02	C	2​	25	35
100	02	C	3​	23	20
100	02	D	1​	15	19
100	02	D	2​	5	10
100	02	C	1​	18	15
100	02	C	2​	12	10
100	02	D	1​	45	50
100	02	D	2​	6	10
100	02	D	3​	4	5
				1,083	1,174

Table2 is an isolated table (no relationships in place) with just 2 rows:

Choice

Category

Product

The measure I presented returns the following when 'Category' is chosen (see results in the ABS Difference column):

Shop	Category	Week	Forecast	Orders	ABS Difference
100	01	1​	260	185	75
100	01	2​	305	295	10
100	01	3​	320	485	165
100	02	1​	123	119	4
100	02	2​	48	65	17
100	02	3​	27	25	2
			1,083	1,174	273

And when 'Product' is chosen, it returns the following:

Shop	Product	Week	Forecast	Orders	ABS Difference
100	A	1​	150	65	85
100	A	2​	175	200	25
100	A	3​	100	110	10
100	B	1​	110	120	10
100	B	2​	130	95	35
100	B	3​	220	375	155
100	C	1​	63	50	13
100	C	2​	37	45	8
100	C	3​	23	20	3
100	D	1​	60	69	9
100	D	2​	11	20	9
100	D	3​	4	5	1
			1,083	1,174	363

So, based on the level of aggregation chosen, you will see a different result at the total level (273 when 'Category' is chosen; 363 when 'Product' is chosen).

The measure I wrote works; I'm just interested to understand whether other methods are possible.

Cheers,

Matty

 

[Matt Allington]

Power BI is not the same as a relational database and you should take a different approach

You should work on you data structure first, then come back to the problem.
Table 1 should not contain category - remove that.
Table 2 should not exist in this format
You need a new table 3 containing the unique product codes (primary key), and Category (and cat code if you like).
Join table 1 to table 3 on product code, 1 to many
hide the product code in table 1 and do not use it.

Use Table 3 columns to add to your visual (axis, table rows, etc)
the measures for orders and forecast are then simply SUM() of the columns. No summarize, calculate, selected value needed

 

[Matty]

Hi Matt,

Thanks for responding.

The thing I'm looking for some direction on does not relate to the 'Forecast' or 'Orders' fields in themselves as such - it's the absolute difference between them at different levels of aggregation that I'm interested in optimising DAX-wise.

I've mocked up a quick PBIX that hopefully illustrate things better. The below snips show the different (but expected) results we get based on the aggregation level chosen:

At Category level:

At Product level:

Measures used are as follows, for the sake of clarity:

Forecast Quantity =
SUM ( Table01[Forecast] )

Order Quantity =
SUM ( Table01[Orders] )

Forecast Accuracy =
1 - DIVIDE ( [ABS Difference], [Order Quantity], 0 )

The 'ABS Difference' measure is as per post #3.

Is there a better way to write the 'ABS Difference' measure? I'm open to simplifying the Fact table and having a separate Dim table for Category if that's the right approach.

Cheers,

Matty

 

[Matt Allington]

Did you build the model as I suggested? Can you please post it online (say dropbox or similar) and share the link here? Regardless, one solution to forecast accuracy would be something like

= 1  - DIVIDE (
               SUMX ( Table01, ABS ( Table01[Forecast] - Table01[Orders] ) ),
               [Order Quantity],
               0
    )

there may be a more efficient version, but I wouldn't attempt that without the model.

 

[Matty]

Hi Matt,

You can get the test *.pbix file I created from this Link.

Happy to be advised otherwise, but I can't see how see this could be achieved any other way. The thing I am interested in is how a table can be built dynamically based on user input, as per my original post.

Cheers,

Matty

 

[Matt Allington]

You need to build the model as I described earlier, then the formula I provided works.

https://www.dropbox.com/s/sz76w3tlhzvbenq/forecast.pbix?dl=1