Grand total of weighted average and measure context

[Jon07]

Hi, 

I am having trouble understanding the row context in Power BI. I have a case where I want to compute the weighted average of a proportion measure by year. Then I want the total of that average to show as a fixed line (same number on all rows by category).

m_w_avg_churn = 
VAR __CATEGORY_VALUES = VALUES('Dim_Date_filtered'[Year])

--RETURN
VAR __w_avg = 
	CALCULATE(DIVIDE(
            SUMX(
                KEEPFILTERS(__CATEGORY_VALUES),
                CALCULATE([test] * [m_Utmeldte])
            ),
            SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE([m_Utmeldte]))
	    ),
    Dim_Date_filtered[Year] <> BLANK()
)

RETURN 
    CALCULATE(__w_avg, ALL(Dim_Date_filtered[Year]))

where test = proportion measure

I tried putting the whole w. avg. calculation in a variable and then remove all filters on that calculation in calculate after. This did not work. 

I also tried different variations of all(), allselected(), and allexcept() in the filter parameter of calculate (see commented out lines below) - this also did not work.

m_w_avg_churn = 
VAR __CATEGORY_VALUES = VALUES('Dim_Date_filtered'[Year])

RETURN
--VAR __w_avg = 
	CALCULATE(DIVIDE(
            SUMX(
                KEEPFILTERS(__CATEGORY_VALUES),
                CALCULATE([test] * [m_Utmeldte])
            ),
            SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE([m_Utmeldte]))
	    ),
        --all(Dim_Date_filtered[Year]),
        --ALLEXCEPT(Dim_Date_filtered),
        --all(),
        --ALLSELECTED(Dim_Date_filtered[Year])
    Dim_Date_filtered[Year] <> BLANK()
)

The only way I got to the final number was to create another measure: 

test_3 = CALCULATE([m_w_avg_churn], all(Dim_Date_filtered[Year]))

Yay - this worked. 

But to me it does not make sense at all as of why this works and the other ones do not. Can anyone shed some light on the concept of context in PBI? Or show how this can be done within the first measure alone. 

Thank you! 

[v-xuding-msft]

Hi @Jon07 ,

1. You also could use quick measure to calculate weighted average.

2. The function of ALL ignores any filters that might have been applied and returns all the rows in a table, or all the values in a column. So the measure "test_3" means calculate "[m_w_avg_churn]" and it don't affected by "year".

Here is a blog about row context and filter context in DAX that you can learn from. When I started learning DAX, I was confused about the context like you. By practicing more, you can understand it better .

Best Regards,

Xue Ding

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

[Jon07]

Hi @v-xuding-msft ,

the first measure in the post is actually based on the quick measure you are referencing, with minor tweaking. 

I do get the desired result in the end. But what baffles me is that test_3 (which is a measure on another measure on m_w_avg_churn) works, but when I do the same logic for test_3 within m_w_avg_churn - the all() function does not want to play with me. 

Thank you for the article. The whole idea of context still needs to sink into my head, but as you said the concepts will (hopefully) come with time.