How can I refer to column created in var dynamically?

2
votes

Pareto

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

2
powerbidax
Can you supply sample data for say 5 rows, and just two variables. Do not show us your model because it is too complex to get quick inside. You can generate it easily by creating new table with Enter data option. Even if someone takes time to analyse it, it will not be usable by others. stackoverflow.com/help/minimal-reproducible-example You say that you are "not able to do it." But why? What happens when you try something. What error do you get? What exactly have you tried? – Przemyslaw Remin
I have added a sample data. I need a measure running total without making a column with rankx. How can I do? – Lukeos93
Please explain why you don't want RANKX? What do you mean by dynamic measure? Can you create the expected result measure (using dreaded RANKX) in the sample PBIX file? It will be easier to start with. – Przemyslaw Remin
I have posted a new sample with all the measure, calculated coloumns and visual. I need a way to filter for district and have my ranking that change – Lukeos93

2 Answers

2
votes

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.

enter image description here

#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/

1
votes

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

Pareto chart with R