Calculating averages for a selection with changing reference

[Anonymous]

Hi, I got a task that I’ve been working on for a couple of weeks now, but did not manage to find a solution for it.

Main purpose is to calculate average and total price values that are filter dependent for a selection.

Initial data model

My dummy data tables:

dim_FL table

PL	SFL	FL	tag	sstm	MC
PL1	SFL-1	FL-1	Tag-1	sstm-1	MC-1
PL1	SFL-1	FL-2	Tag-2	sstm-1	MC-2
PL1	SFL-1	FL-3	Tag-1	sstm-2	MC-2
PL1	SFL-1	FL-4	Tag-3	sstm-4	MC-3
PL4	SFL-2	FL-5	Tag-3	sstm-5	MC-4
PL4	SFL-2	FL-6	Tag-3	sstm-6	MC-3
PL4	SFL-2	FL-7	Tag-1	sstm-5	MC-2
PL2	SLF-3	FL-8	Tag-2	sstm-1	MC-2
PL2	SLF-3	FL-9	Tag-3	sstm-5	MC-3
PL2	SLF-3	FL-10	Tag-4	sstm-1	MC-4
PL2	SLF-3	FL-11	Tag-2	sstm-4	MC-1
PL2	SLF-3	FL-12	Tag-3	sstm-4	MC-2
PL2	SLF-3	FL-13	Tag-3	sstm-1	MC-2
PL2	SLF-3	FL-14	Tag-4	sstm-1	MC-3
PL1	SFL-1	FL-15	Tag-4	sstm-2	MC-4
PL1	SFL-1	FL-16	Tag-1	sstm-4	MC-1
PL1	SFL-4	FL-17	Tag-3	sstm-1	MC-4
PL1	SFL-4	FL-18	Tag-3	sstm-4	MC-1

fact_price table

FL	Tag	color	MAT	price_pm	price_cm
FL-1	Tag-1	col1	mat1	0,4	0
FL-2	Tag-2	col2	mat2	1,1	0
FL-15	Tag-4	col1	mat3	0,5	0
FL-16	Tag-1	col3	mat1	0,7	0
FL-8	Tag-2	col5	mat2	0	0,6
FL-10	Tag-4	col2	mat2	1,8	0
FL-11	Tag-2	col4	mat3	0,01	0
FL-12	Tag-3	col5	mat1	0,05	0
FL-8	Tag-2	col5	mat2	0,95	0
FL-1	Tag-1	col1	mat1	0	0,09
FL-15	Tag-4	col8	mat2	0	0,5
FL-11	Tag-2	col11	mat3	0	1,1
FL-17	Tag-3	col2	mat2	0	0,8
FL-18	Tag-3	col4	mat1	0,4	0
FL-18	Tag-3	col1	mat1	0	0,9

Task 1: Calculate average values for related tags under selected SFL for all same tags in fact_price table. price_pm average should be calculated only for those FLs with non-zero price_pm value in fact_price table. price_cm average should be calculated for all FLs from dim_FL table.

Task 2: Calculate total (averaged though) price for the amount of FLs related to the selected SFL.

Task 3: There should be a possibility to modify the reference of an average value, i.e. to filter by PL, sstm, MC, color or MAT in order to include only related FLs. In this case the amount of FLs should also change related to the filter.

My attemp:

Currently where I am stuck is I do not know how to create a dynamic list / summarization that can be passed on as a filter to another table. Also, to be able to filter (Task 3) I duplicated FL table (called WL FL), so I am currently working with such data model:

model with duplicated FL table WL FLBelow is how I would calculate average price_pm per FL:

PM pr FL= 
var SelSupFL = SELECTEDVALUE( 'dim_FL'[SFL] )
var summarizedTags =  
    SUMMARIZECOLUMNS('dim_FL'[tag], "TAGGY", 
            FILTER('dim_FL', 'dim_FL'[SFL] = SelSupFL) )
var NoFLs = CALCULATE( 
    DISTINCTCOUNT( fact_price[FL]), 
                ALL( 'dim_FL'[SFL]), 
                fact_price[price_pm]>0 )
                
return

DIVIDE(
    CALCULATE( 
            SUM( 'fact_price'[price_pm]), 
            FILTER( 'fact_price', 'fact_price'[Tag] IN summarizedTags ) ) ,
    NoFLs , 0)

I know that IN should be used with lists, however, I do not know how to convert the column [TAGGY] from the summarized table into a list of values. Also, when I tried to use VALUES() for this column, I was not able to refer to it.

My another thought instead of using a list, was to check if a certain tag value from fact_price table exists in the [TAGGY] column, but again I could not refer to the [TAGGY] column.

Also, calculation for average price_cm should be similar, but taking into account number of all FLs depending on selection (crossfilter added, because of single relation):

NoFLcm =
  CALCULATE(
     DISTINCTCOUNT( 'WL FL'[FL] ), ALL( 'dim_FL'[SFL] ), 
     CROSSFILTER('fact_price'[FL], 'WL FL'[FL], Both ) )

My questions:

1) How to pass dynamically filtered values as a filter to another table?

2) Regarding Task 3, if there is a way not to duplicate FL table and still be able to filter? (the table is pretty large, so I would gladly give it up if there is a better way)

----

MORE DETAILS:

Logic of the task:

Step A. Select a value of SFL (visual filter from dim_FL table)

Step B. Make a list / summarize tag values related to the selected SFL

Step C. Use those tag values as a filter to calculate average values for all same tags in fact_price table. Also calculate total value for a selection.

Model description:

A part of our data model is shown below. Relation is through FL column (single 1 to many relation), that is unique for dim_FL table. fact_price table can have several FL entries with different price_pm and price_cm values depending on several criteria (such as MAT and color). Also, a single row in fact_price table has either non-zero price_pm or non-zero price_cm value (i.e. if pm_price=0, then always cm_price<>0, and vice versa). Not all FLs from dim_FL table have price_pm and/or price_cm values, so some FLs do not appear in the fact_price table.

One PL can have several SFL, and SFL belongs to a single PL.

One SFL can have several FLs, and FL belongs to a single SFL (so, there is a hierarchy PL – SFL – FL)

A FL has a single Tag. Tags can be the same for different FLs.

Other columns are interesting from filtering perspective only.

Task 1 example: 

If we select SLF-2 that has three FLs with Tag-1 and Tag-3, then we need to calculate average price values for all Tag-1 and Tag-3 tags from fact_price table.

In this case Avg. price_pm value for Tag-1 would be: (0.4 + 0.7) / 2 = 0.55 (divided by a distinct count of FLs of Tag-1 with non-zero price_pm value, i.e. 2)

Avg. price_cm value for Tag-1 would be: (0.09) / 4 = 0.0225 (divided by a total number of FLs with Tag-1 from dim_FL table, i.e. 4)

Avg. price_pm value for Tag-3 would be: (0.05 + 0.4) / 2 = 0.225

Avg. price_cm value for Tag-3 would be: (0.8 + 0.9) / 8 = 0.2125

Task 2 example:

If we take an example of selecting SLF-2, it has one FL with Tag-1 and two FLs with Tag-3.

So, total values would be:

Tag-1 total price_pm: 0.55 * 1 = 0.55 (multiplication by 1 because SFL-2 has one FL with Tag-1)

Tag-1 total price_cm: 0.0225 * 1 = 0.0225

Tag-3 total price_pm: 0.225* 2 = 0.45 (multiplication by 2 because SFL-2 has two FLs with Tag-3)

Tag-3 total price_pm: 0.2125 * 2 = 0.425

So final total price for selected SFL are the following:

price_pm : 0.55 + 0.45 = 1.0 (summing up total price_pm values the ones above)

price_cm : 0.0225 + 0.425 = 0.4475

Task 3 example:

I we want to calculate average price value in respect to PL1, then we would include only these FLs that are related to PL1. In this case there are 3 FLs with Tag-1 and 3 FLs with Tag-3 in dim_FL table. In this example we would need to take into account Tag-1 and Tag-3 values and amount from FL-1, 2, 15, 16, 17, 18 only (in fact_price table), because they are related to PL1.

[v-stephen-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Stephen Tao

[Anonymous]

Hi @v-stephen-msft ,

Thank you very much for your reply, and my appologies for my late reply due to Christmas period.

I found your comments and suggestions above very useful and I used them to a great extend to modify my formulas and data model. However, on the way I faced other challenges that I guess should be addressed in a separate topic.

Best regards,
Ilma

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Sorry for my late reply.

I did not establish a relationship between the two tables to avoid creating a third table.

New measures of Task 3:

(PL)Average price_cm = 
IF (
    ISFILTERED ( 'dim_FL'[PL] ),
    DIVIDE (
        CALCULATE (
            SUM ( 'fact_price'[price_cm] ),
            FILTER ( 'fact_price', [FL] IN VALUES ( 'dim_FL'[FL] ) )
        ),
        CALCULATE (
            COUNT ( 'dim_FL'[tag] ),
            FILTER ( 'dim_FL', [tag] = MAX ( 'fact_price'[Tag] ) )
        )
    )
)

(PL)Average price_pm = 
IF (
    ISFILTERED ( 'dim_FL'[PL] ),
    DIVIDE (
        CALCULATE (
            SUM ( 'fact_price'[price_pm] ),
            FILTER ( 'fact_price', [FL] IN VALUES ( 'dim_FL'[FL] ) )
        ),
        CALCULATE (
            COUNT ( 'fact_price'[Tag] ),
            FILTER (
                'fact_price',
                [Tag]
                    IN VALUES ( 'dim_FL'[tag] )
                        && [price_pm] <> BLANK ()
                        && [FL] IN VALUES ( 'dim_FL'[FL] )
            )
        )
    )
)

I added IF (ISFILTERED ( 'dim_FL'[SFL] ), to other measures. 

For more details, you can check from here.

Best Regards,

Stephen Tao

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

[v-stephen-msft]

Hi @Anonymous ,

Your explanation is very good, I basically completed it according to your explanation, but I still don't understand the Task 3 example, I hope it can be explained clearly like the explanation above.

This is the PBIX file that completes Task1 and Task2.

Best Regards,

Stephen Tao

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

[Anonymous]

@v-stephen-msft ,

I agree I could have been clearer about the Task 3. Also, I want to add that the report page should be able to perform all Tasks 1, 2, and 3 simultaneously when filtered.

As for the pbix file you shared, I noticed that both tables were not connected, while they should have 1-to-many single relationship (in reality they are a part of a bigger data model). Once I created such connection in the file you shared, the calculation failed. Is there a way to create similar calculations when the relationship between the tables exists?

As for Task 3, let me clarify it with an example. Let's say on a report page we have visual filters PL, SFL, sstm and MC from dim_FL table, visual filters color and MAT , and some other visuals to show results of calculated average and total price values. Let’s say, we want to calculate average price values for SFL-2 as before (single selection of a visual filter) but this time we want to calculate values in respect to PL1 (can be a multiple selection in visual PL filter). As before, we need to calculate values for Tag-1 and Tag-3, but now in the calculations there should be included only those values that "belong" to PL1. In this case:

Average price_pm for Tag-1 should be: ( 0.4 + 0.7 ) / 2 (dividing by a distinct count of FLs with Tag-1 with non-zero pm price, i.e. 2)

Average price_cm for Tag-1 should be: ( 0.09 ) / 3 (dividing by all FLs with Tag-1 from PL1)

Average price_pm for Tag-3 should be: (  0.4 ) / 1

Average price_cm for Tag-3 should be: ( 0.8 + 0.9 ) / 3

When PL1 is selected, calculation should include only related (greyed out) rows

Total values should be calculated as described earlier taken into account these “new” reference averages, i.e. SFL-2 has 1 FL with Tag-1 and 2 FLs with Tag-3.

Also, same filtering / changing reference behavior should apply when selecting other visual filters (MC, sstm, color and/or MAT), i.e. averages and totals should be calculated taken into account the selection.

As I indicated in my original post, to be able to do the described filtering, I duplicated dim_FL table (called WL FL table, and created also 1-to-many single relationship with fact_price table). However, I wonder if there is another / better way to do the calculations and filtering.

Thanks.