Create a custom table combining multiple measures

[savage63073]

I have a scenario, might be too complex to ask for a direct solution here, but am looking for advice on whether it is possible and what solutions I should go explore and learn.

The scenario is, I am trying to create a table visualization with data that comes from a combination of data stored in tables + the results of a variety of different measures.

We achieve this as depicted by creating a bunch of different cards and lining them up visually to appear like a single table. The shortcoming of that solution is that it doesn’t leverage the features of a table visual in the report, such as the ability to download the data set into Excel.

So we are looking to construct a table with the rows and columns coming from the different measures so that we can use the standard table visual in Power BI to depict it. Also essential is that it remains adaptive to any filters used (the measures work in that way already as they are written with USERELATIONSHIP – I just need it to continue to respond to filters if I can get it into a table as we need).

I’ve been playing around with CALCULATETABLE, UNION, etc., but I don’t seem to be getting to something that works. So my question is, can this even be done? What commands should I be learning? Any articles or YouTube videos you’d recommend?

Thanks!

[savage63073]

UPDATE: I don’t have a solution yet, but I’ve learned a few things, have hit a specific limitation and now at least may be able to form a more specific and targeted question that I will ask here.

I learned a way to create the table that I need which constructs its content based on some various measures that I am pulling in.  A simplified version of it is as follows:

TestTable =

  {

("TARGET: % of Revenue from subscription renewals", [January Target % of Revenue], [February Target % of Revenue], [March Target % of Revenue]),

("ACTUAL: % of Revenue from subscription renewals", [January Actual % of Revenue], February Actual % of Revenue], [March Actual % of Revenue]),

("ACTUAL: Amount of Revenue from subscription renewals", [January Renewal Revenue], [February Renewal Revenue], [March Renewal Revenue]),

("ACTUAL: Amount of Overall Revenue", [January Overall Revenue], [February Overall Revenue], [March Overall Revenue])

  }

This kind of works.  It works in the sense that I can use it to display the correct and accurate information in the single table visual that I need.  So, yay!

The problem is that it doesn’t respond to filters on the page.  The measures are written correctly, and if I display them individually on a simple card then they do respond to the page filters.  But when the measures are used within this table as I’ve built, they do not.

So my hopefully better question is, how do I build a table in this fashion, leveraging measures as I need to, but get the measures used to populate the table to respond to filters?

Appreciate any direction anyone can provide – thanks!

[amitchandak]

@savage63073 summarizecolumns will work best in this case

https://www.sqlbi.com/articles/introducing-summarizecolumns/

[savage63073]

Thanks mate, I'll give that a shot.