Dynamic measure formatting in charts using Calculation Groups

DAX DAX Calculations
1

Hi all,

I like to offer my users the ability to dynamically select from one of many measures to be displayed in the same chart. This works fine if all the measures on offer return the same format, but if they are of differing formats the charts cannot handle the varying formats and do not display correctly.

The new Calculation Groups functionality supposedly resolves this issue, but I am struggling to get it to work.

Please see my sample report: Calculation Groups Query.pbix (441.6 KB)

I have the 3 measures below, each with a different format:

  • Order Qty - Whole Number
  • Margin Percentage - Percent
  • Sales Amount - Currency

The top half of my page shows these in their standard format within a matrix and in 3 individual static charts, each correctly displaying their respective format:

image
image1198×529 47.3 KB

Following guidance from the video below I have tried to utilise calculation groups to allow me to fix this issue:

In Tabular Editor, I have created a calculation group ‘User Options’ with 3 calculation items, each containing a copy of the DAX used for their associated measure (eg not referencing the original measures themselves) and with each returning an appropriate Format String:

  • CG Margin %,
  • CG Order Qty
  • CG Sales Amount

image
image714×507 31.1 KB

This adds a new ‘User Options’ table to my data model containing a single column ‘Measure to View’.
I have added this column to a slicer to allow the user to select one of the 3 measures to chart.
I have then created a new ‘Dynamic Chart’ in the bottom page of my report, which uses a dummy measure for values and the calculation group ‘Measure To View’ in Legend.

This now allows my single chart to dynamically return each of the 3 measures, each correctly formatted

Margin % :smiley:

image
image1008×210 15.8 KB

Sales Amount :smiley:
image
image1015×208 15.8 KB

HOWEVER while it works a treat in my dynamic chart, it breaks my original matrix table and standalone charts, where all measures return the same value as my selected Measure to View?:

image
image1298×737 62.3 KB

I don’t understand why this is happening as the matrix and 3 static charts are all using measures which are standalone from my calculation group?

It wouldn’t be a problem if I wasn’t displaying the matrix and my page simply displayed the dynamic chart and slicer, however this isn’t realistic. I need to be able to use the dynamic chart and the standalone measures on the same page.

The only solution I have found so far is to use Edit Interactions and set my matrix and 3 charts to ignore my Measure to View slicer.

I appreciate this is a long post and this is relatively new and different functionality I am trying to use, but Calculation Groups are touted as being the fix to this long standing problem and I would love to get it working!!

Many thanks in advance

Mark

2

Hello Mark,

In order to solve this issue you just need to click on the “Measure to View” filter (that is supposed to be used only with the dynamic chart) and go to Format/Edit Interactions and disable the interactions with the other visuals as in the print screen below.

image
image2071×1153 496 KB

I think that the behavior of the calculation groups might evolve to automatically ignore the other visualizations on the page and not impose the format everywhere. In the meantime, the fix is easy with edit interactions. It is also a best practice to always limit the impact of the filters to only the graphs/charts needed.

1 Like
3

Hi Mark,

The solution that using Edit Interactions is quite inconvenient if you try to reuse this filter. So my solution is using 3 functions are SELECTEDMEASURE, ISSELECTEDMEASURE, and SELECTEDMEASUREFORMATSTRING as the example below.

image
image791×54 2.88 KB

These functions will help you to track which chart is using “DummyMeasure” and correct the format that you want.

I hope this helps! :slight_smile:

Calculation Groups Query.pbix (442.1 KB)

1 Like
4

Hi @Mark, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

5

Hi @Mark, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

6

How would you format say the Margin % column only if for example it had negative % values so that the negative % values are red an the positive values are green?

7

you might want to start a new topic as this thread has a solution marked to it. your question might not be answered @Harvey

1 Like
8

Sure. thanks