Forum Discussion
Find first value above 50% by category using DAX
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
_cumulativepercentageFirst value above 50%: =
IF (
[Income cumulative ratio:]
= MINX (
FILTER (
ADDCOLUMNS ( ALL ( Data[Customer] ), "@ratio", [Income cumulative ratio:] ),
[@ratio] > 0.5
),
[@ratio]
),
[Income:]
)
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_Kim3 years ago
Super User
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.