Can someone explain how to use the disconnected table technique to use measures in the X-Axis?

[Anonymous]

Hi Everyone

I have viewed this post and feel like it contains the info I need, but I am having a hard time parsing the DAX and applying it to my situation.

I am trying to build a column chart that would allow me to use measures in the X-Axis. My report contains budget vs actual information. I have 3 measure that I would like to display as separate columns. I would also like this to change dynamically based on Team selected fro a slicer. 

My measures are essentially:

Forecast 1 = [total revenue] - [Expected Cost 1]

Forecast 2 = [total revenue] - [Expected Cost 2]

Forecast Target = averagex(budget table, budget table [forecast targets])

Can anyone help?

thanks

GC4002

[Anonymous]

Hi @Anonymous ,

Can you show some sample data and expected result to us like the post you shared with us?

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Not where clear why you need the disconnected table . Can you please explain with an example.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

few more example

https://www.blue-granite.com/blog/disconnected-table-power-bi

https://www.kasperonbi.com/dynamic-data-comparisons-using-disconnected-slicers-treatas-and-inactive-relationships/
Appreciate your Kudos.

[AllisonKennedy]

I haven't looked at the link you provided yet, but can you use the three measures in values?

[Anonymous]

Hi @AllisonKennedy 

I could use them as values, my original version had this set up. So while this makes sense when I have my various teams as categories, if I drill down to a single team using a slicer the visual looks strange as all the columns are bunched together, being a single category.

If i had the measures as separate categories the graph would be more visually appealing and ultimately of better use to the end user. I think the link in my original post gives a way of doing this, but I'm having a hard time translating the example DAX to my specifics.

[AllisonKennedy]

Ok, if you want specific help then we'll need to know the names of your tables and columns and rough preview of the data. We will also need to know what level of granularity you wish to display.
I think you will need to create a disconnected table that has:
Axis
Forecast 1
Forecast 2
Forecast Target

Then use the SWITCH Measures to Show sample to return the Forecast 1 measure if Forecast 1, etc, etc.

[Anonymous]

Hi @AllisonKennedy 

I have the following tables / fields:

Team List [Team name]

Forecast Measures [forecast 1], [forecast 2]

Budget Summary [forecast target]

In terms of granularity, I'd like to show individual teams selected by slicer. The forecast measures have been created by taking our actual spend to date, adding on our remaining budget spend to completion.