Find first value above 50% by category using DAX

[dschin02]

Dear DAX Brains Trust!

I'm trying to write a column in DAX that returns the first value above 50% within a categorised cumulative column.

For context I've got a big dataset off all the postcodes around Australia and with each postcode comes an income category, 14 to be precise, thanks to the Australia Bureau of Statistics (ABS).

I've been through an arduous but insightful process via power query in which I have organised them by creating a list.generate function which has returned each income category within the postcode with cumulative running total of income % - Thanks to @RickdeGroot (BI Gorrilla) and possibly the GOAT of M code.

I've now got my data how I want it, and as a final step would like to create a measure or column. That returns the first value over 50% within the cumulative running for each postcode.

I'm a bit stumped at how I achieve this, I've tried RANKX & TOPN but with very little success.

Will post a screenshot of the data and attempted DAX code tomorrow. Any advice beforehand would be greatly appreciated.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your datamodel.

Income: = 
SUM( Data[Income] )

Income cumulative ratio: = 
VAR _runningtotal =
    CALCULATE (
        [Income:],
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ADDCOLUMNS (
                SUMMARIZE ( ALL ( Data ), PostCode[PostCode], Data[Customer] ),
                "@income", [Income:]
            ),
            ORDERBY ( [@income], ASC ),
            KEEP,
            PARTITIONBY ( PostCode[PostCode] )
        )
    )
VAR _allincomebypostcode =
    CALCULATE ( [Income:], ALL ( Data[Customer] ) )
VAR _cumulativepercentage =
    DIVIDE ( _runningtotal, _allincomebypostcode )
RETURN
    _cumulativepercentage

First value above 50%: = 
IF (
    [Income cumulative ratio:]
        = MINX (
            FILTER (
                ADDCOLUMNS ( ALL ( Data[Customer] ), "@ratio", [Income cumulative ratio:] ),
                [@ratio] > 0.5
            ),
            [@ratio]
        ),
    [Income:]
)

[dschin02]

Thanks for this, It's very helpful.

Your code seems to work and it is what I am looking for. However when I run it it returns all atribute values above 50%. Whilst I am only seeking the first value above 50% categorised by postcode. See screenshot below. I've underlined the postcode category types and the value I want it to return.

[Jihwan_Kim]

Hi,

Thank you for your message.

My solution was for writing calculated measures, not for calculated columns.

I am not 100% sure, but it might need to be written in a different way based on how your data model looks like.

Thanks.