topic Re: Find first value above 50% by category using DAX in DAX Commands and Tips

Find first value above 50% by category using DAX

dschin02 — Tue, 14 Mar 2023 13:17:33 GMT

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.

Re: Find first value above 50% by category using DAX

Jihwan_Kim — Tue, 14 Mar 2023 17:34:11 GMT

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:] )

Re: Find first value above 50% by category using DAX

dschin02 — Wed, 15 Mar 2023 02:15:24 GMT

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.

Re: Find first value above 50% by category using DAX

Jihwan_Kim — Wed, 15 Mar 2023 04:17:36 GMT

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.