Forum Discussion

dschin02's avatar
dschin02
Frequent Visitor
3 years ago

Find first value above 50% by category using DAX

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.

above
DAX
FIRST
value

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      dschin02
      Frequent Visitor
      3 years ago

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        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.