CODEWITHSUNDEEP
powerbidaxpowerquery
Lorenzo Benassi
Lorenzo Benassi

Reputation: 621

DAX - Histogram for relative frequencies and total column

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: enter image description here

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

Upvotes: 1

Views: 553

Answers (2)

Lorenzo Benassi
Lorenzo Benassi

Reputation: 621

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!

enter image description here

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40204

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] )
        )
    )
)

Line Bar Chart

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.

Waterfall Chart

Upvotes: -1

Related Questions