Dynamic weighted median for Income

[Anonymous]

Hello...

I have the following prosedure to calculate median as SSPS algorithim do but Dynamicly through DAX

 all families must have value for all Income categories if the no income then record it by 0

so the Data Shape like

Family_ID	INCOME_CAT	INCOME_DESC	VALUE	Weighted MEMBERS NUMBER	UnWeighted MEMBERS NUMBER
1	1	Main Job	2000	5	2
1	2	Second Job	0	5	2
1	3	Real estate	3000	5	2
2	1	Main Job	1000	10	4
2	2	Second Job	500	10	4
2	3	Real estate	5000	10	4

A- find Per Capita Income:

1- Sum the income value for each Family/UnWeighted MEMBERS NUMBER

2- Step 1 * Weighted MEMBERS NUMBER

3- Step 2/(Total distinct Weighted MEMBERS NUMBER)

the equation that give right result and its totaly dynamic is :

:=
SUMX (
    SUMMARIZE (
        FACT_HOUSEHOLD_INCOME,
        FACT_HOUSEHOLD_INCOME[FAMILY_ID],
        "M", AVERAGE ( FACT_HOUSEHOLD_INCOME[Memebers_Number] ),
        "Wi", AVERAGE ( [wi] ),
        "IN", SUMX (
            SUMMARIZE (
                FACT_HOUSEHOLD_INCOME,
                FACT_HOUSEHOLD_INCOME[FAMILY_ID],
                FACT_HOUSEHOLD_INCOME[INCOME_CAT],
                "INN", SUM ( FACT_HOUSEHOLD_INCOME[VALUE] )
            ),
            [INN]
        )
    ),
    IF ( [m] <> 0, ( ( [IN] / [M] ) * [Wi] ), 0 )
)/CALCULATE([Total Members],all(DIM_Family_CAT))

that was a Per Capita Income

I need help finding Median which is

1- Sort Families by Per Capita Income from lowest to highst

2-sum a cumulative (Weighted MEMBERS NUMBER)/max(cumulative (Weighted MEMBERS NUMBER))*100

3- from step 3 take the nearst value to 50

can any one help me to use the previous equation to reach the median and to be dynamic for the Category and family

thanks in advance

[v-danhe-msft]

Hi @Anonymous,

From your description, I could not understand below sentence:

1- Sort Families by Per Capita Income from lowest to highst
What is the desired result from this step?

2-sum a cumulative (Weighted MEMBERS NUMBER)/max(cumulative (Weighted MEMBERS NUMBER))*100

Is this step get the value from ((5+5+5+10+10+10）/（2+2+2+4+4+4))*100=250?

3- from step 3 take the nearst value to 50

If above steps are step 1 and step 2, what step is step 3?

Could you please offer me more information and post your desired result?

Regards,

Daniel He