Forum Discussion
Calculating averages for a selection with changing reference
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.
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 ,
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.