DAX - Histogram for relative frequencies and total column

Question

I built a diagram that displays the relative frequencies of my clusters (on the values in the column) and the cumulated frequencies (on the values on the line). My chart has this aspect:

I would like to add a new column to the right that is equal to the sum of all the values of the previous columns. The relative frequencies are created using the code below:

"Frequencies UL" :=
CALCULATE (
    DISTINCTCOUNT ( 'table1'[Column1] );
    USERELATIONSHIP ( 'Order Cluster'[Cluster Name]; table1[tag Cluster] );
    SUMMARIZE ( table1; table1[tag Cluster] )
)

I would really appreciate some help!

Thanks

Sounds like a [Cumulative Total](https://www.daxpatterns.com/cumulative-total/) pattern. — Alexis Olson, Dec 20 '18 at 16:49
@AlexisOlson I I had already seen the article, but how I can create a column with the sum of the previous columns? — Lorenzo Benassi, Dec 20 '18 at 17:13
What do you have on the x-axis? That's what you use in the cumulative total unless it's categorical, in which case you need to use an index that gives the order you want. — Alexis Olson, Dec 20 '18 at 18:12
Yes on the x-axis I have a categorical class and I have order it with an index. In the sixth class I indicated "total" but I can not create a relationship with the clusters and total — Lorenzo Benassi, Dec 21 '18 at 08:40
I have already built the measure for the cumulated sum in this way: `Cumulative Freq UL = CALCULATE(SUM('tab1Freq'[Frequencies UL]);USERELATIONSHIP( 'Order Cluster'[Cluster Name]; table1[tag Cluster] );FILTER(ALLSELECTED('tab1Freq'); 'tab1Freq'[Order Cluster] <= MAX('tab1Freq'[Order Cluster])))` — Lorenzo Benassi, Dec 21 '18 at 09:10
Doesnt seem an optimal visualisation design. As shown in Alexis' mockup below you'll compress the detail of the actual histogram charts, with the only useful extra info being the tiny data label. A separate Card visual would probably be better, showing just `DISTINCTCOUNT ( 'table1'[Column1] )` — Mike Honey, Dec 28 '18 at 00:28

score 0 · Accepted Answer · answered Dec 28 '18 at 10:11

0

Simply it was necessary to do this:

"Frequencies UL" := IF(SELECTEDVALUE('Order Cluster'[Is Total]);
CALCULATE(DISTINCTCOUNT ('table1'[Column1]); ALL('Order Cluster')); DISTINCTCOUNT('table1'[Column1]))

And this is the result I got!

answered Dec 28 '18 at 10:11

Lorenzo Benassi

621
1
8
31

Wait, you already had a total row in your `'Order Cluster'` table? That would have been hugely useful information to provide in the question since that's really the hardest part of making this work. With that in place, the question is completely different and much simpler. – Alexis Olson Dec 28 '18 at 20:45

score -1 · Answer 2 · answered Dec 27 '18 at 18:51

I'd suggest creating a new table to use for your x-axis.

If your 'Order Cluster' table looks like this:

ClusterName  Order
ClusterA     1
ClusterB     2
...          ...
ClusterZ     26

You want to add a Total row to the end so try something along these lines:

NewTable = UNION('Order Cluster', {("Total", MAX('Order Cluster'[Order]) + 1)})

Use NewTable[ClusterName] for your chart's x-axis and tweak your cumulative measure to reference NewTable[Order] in the FILTER inequality.

You'll also need to adjust your frequency measure to handle the case when you have the Total cluster (use an IF or SWITCH) and make sure you're evaluating within the correct filter context. Something like this logic:

IF( MAX( NewTable[ClusterName] ) = "Total",
    CALCULATE( [Frequency Calc], ALLSELECTED( table1 ) ),
    CALCULATE( [Frequency Calc],
        FILTER(
            ALLSELECTED( table1 ),
            table1[tag Cluster] = MAX( NewTable[Order] )
        )
    )
)

P.S. You might be better off adding the total row to your 'Order Cluster' table in the query editor instead of having another table floating around. In any case, the logic is similar; add a total row to the column you're using for your axis and adjust your measures to handle that category how you want.

A waterfall chart might be another option to consider (and doesn't need nearly as much work), though I don't know that you can include the percent info except in the tooltip.

DAX - Histogram for relative frequencies and total column

2 Answers2