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.Sales Amount at transaction level (aggregate at higher level) measure
Hello, I am trying to create a measure that displays the Sales Amount at transacttion (opportunity) level, regardless of the other dimensions I pull in my report. This can be a calculated column and it will work just fine, but I need to use a parameter with this calculation in the future so it HAS TO BE A MEASURE. When I do this calculation: Sales Amount Oppty Level = CALCULATE([Sales Amount],REMOVEFILTERS(Prod[ProdName]),VALUES(Oppty[OpptyName])) i get the accurate amount at opportunity level, but, if i add dimensions to the report, I get a cartesian product (all dimension values x all dimension values). ALLEXCEPT works only when I use the Product Table Oppty Name dimension, but I need to use Oppty Name from Product Table and the Partner dimension will make cartesian product with Opportunity for some reason. Cannot Use Oppty Name from Oppty table since then the measure will show the total sales amount for all Oppty for every row. Sales Amount Oppty Level 2 = CALCULATE([Sales Amount],ALLEXCEPT(Prod,Prod[OpptyName])) If i switch ALLEXCEPT to the below: Sales Amount Oppty Level 3 = CALCULATE([Sales Amount],ALLEXCEPT(Oppty,Oppty[OpptyName])) I will get the Sales Amount, not the Oppty total for all rows. Please help me to see the total Oppty Amount in a visual where I can have Oppty Name, Prod Name, Partner without cartesian product, like (red is bad, since partners 1 and 2 are not connected to oppty 1): Please help! Here is the model
