How can I refer to column created in var dynamically?

Question

I have created a pareto analysis but the problem is that it's not dynamic because the rankx it's done in a calculated column in customers table in order of the sum of sales in an other table.

Now my #runningtotal is

CALCULATE([M-CY_Sales];FILTER(ALLSELECTED(CUSTOMERS);
    CUSTOMERS[DAX RANK]<=MAX(CUSTOMERS[DAX RANK]));CUSTOMERS[Customer Number] <>BLANK();
    'Detail Sales Report'[Total Actual Revenue - Base]>0)

where I use the calculated column with rankx CUSTOMERS[DAX RANK]. Can I make this measure dynamic? I was thinking to build a table with var and addcoloumn but I'm not able to do it. My actual problem is that I need this pareto dynamic because the filter for district does not function with static column.

I was trying to write something but I don't know how I could create what I want

#RUNNINGTOTAL2 =
var customerranked=ADDCOLUMNS(ALLSELECTED(CUSTOMERS);"ranking";[M-DAX RANK])
return
CALCULATE([M-CY_Sales];FILTER(ALLSELECTED(CUSTOMERS);
    customerranked<=MAX(customerranked));CUSTOMERS[Customer Number]<>BLANK();
    'Detail Sales Report'[Total Actual Revenue - Base]>0)

Obviously this is not correct. I hope you understand my problem. I need to refer a virtual column done with rankx in my measure running total

Sample data edited with measures: [here]: https://mega.nz/#!4t1y0AJI!XF2Vcejm6C50nnssQCS1bJEhnqIGiH1d-mIltVskRgE

Answer 1

While here is the PBIX file and it may work as you expected, but you should take a broom and sweep your model a little. To get it working just set up the relationship from District to Customer and then to Sales. Or even better, get rid of Districts table. You have that dimension in Customers table. I just slightly changed your measures to get it working but I would change them altogether. Probably you do not need to use FILTER function.

#RUNNINGTOTAL = 
CALCULATE (
    SUM ( 'Sales'[Revenue] ),
    FILTER (
        ALLSELECTED ( Customers ),
        Customers[DAX RANK]
            <= MAX ( Customers[DAX RANK] )
    ),
    'Sales'[Revenue] > 0
)

Anyway I would start it from scratch. Why do you have three tables? What is the purpose of table Districts. You can use the Districts form table Customers to slice Sales.

If you really do not accept corrected invoices and negative sales (ask yourself why), build a measure like that:

[Sales] =
CALCULATE (
    SUM ( FactTable[Sales] ),
    FactTable[Sales] > 0
)

And then refer to it in other measures. Check these posts to see differences of filtering:

DAX Calculate function with and without FILTER

Difference between CALCULATE(m, x=red) vs CALCULATE(m, KEEPFILTERS(x=red))

You may think of building a bridge table, between Customers and Sales, which will contain unique CustomerID of both tables. Dictionaries are updated with lag.

bridge =
DISTINCT (
    UNION (
        DISTINCT (     Sales[CustomerID] ),
        DISTINCT ( Customers[CustomerID] )
    )
)

Give it a shot: https://www.daxformatter.com/

Answer 2

It is indeed possible, and encouraged to define measures that calculates ranks and cumulative totals on the fly.

However, there are some visualization issues. It looks not possible to use a measure for x axis with "Line and clustered column chart". So it would not be possible to use the Rank measure for x axis. You may put Customer Number to x axis instead, however the chart will look badly with a categorical x axis. It will not fit in the screen and will require a long scroll to reach the right end. Practically, this will hardly work as a pareto chart.

On the basis of this observation, I suggest to use R / Python visual if possible. Here is an example with R visual.

library(dplyr)
library(ggplot2)

totalSales <- sum(dataset$SalesAmount)

dataset <- dataset %>%
    arrange(desc(SalesAmount)) %>%
    mutate(
        CumulativeSales = cumsum(SalesAmount),
        Rank = order(SalesAmount, decreasing = TRUE)
    )

p <- ggplot(dataset, aes(x = Rank, y = SalesAmount)) +
    geom_bar(stat = "identity", width = 1, fill = "#01b8aa")

ymax <- layer_scales(p)$y$range$range[2]

p <- p + geom_line(aes(y = CumulativeSales / totalSales * ymax),
                   size = 1, color = "#fd625e") +
    scale_y_continuous(sec.axis = sec_axis(~ . * totalSales / ymax)) +
    theme_bw()

p