Temporary custom tables? (Plot grand total)

[Booth070]

Hi

Since PBI does not offer the option to plot a grand total when plotting a measure by groups, I am creating custom tables to achieve the result:

1. Create custom summary table by group (Table 1)

2. Create custom summary table of Table 1 (calculates grand total = Table 2).

3. Add column to Table 2 with group name = "Total"

4. Use UNION() to obtain final table (Table 3)

I wouldn't mind doing this if I had one measure to plot but I have a few and using this method I am going to end up with a couple dozen tables. 

I was wondering if it was posible to avoid creating the first two tables and use a syntax similar to "var temp" instead?

(Or, please, if you know a more effective/quick manner to plot totals, that would be really welcomed)

Thanks in advance!

[v-xjiin-msft]

Hi @Booth070,

First since you didn't share any sample. I don't understand why you need to create two summary table then union them. Please share us some sample if possible. So that we can understand your requirement more clearly.

Then it is possible to define a table variable in the expression. Please refer to my sample:

Table 3 =
VAR temp1 =
    SUMMARIZE (
        Sheet1,
        Sheet1[Resource Name],
        Sheet1[RecordType],
        "Actual", COUNT ( Sheet1[Actual(h)] )
    )
VAR temp2 =
    SUMMARIZE (
        Sheet1,
        Sheet1[Resource Name],
        Sheet1[RecordType],
        "SUM Actual", SUM ( Sheet1[Actual(h)] )
    )
RETURN
    UNION ( temp1, temp2 )

Thanks,

Xi Jin.

[Booth070]

Sorry for not adding an example. Here´s a dummy file with it. 

@Lind25 indeed I want to add a row for the "average Days" of the total. I need the resulting plots to be responsive to the date slicer (in other examples, there are a couple more slicers they need to respond to).

 The measures I am using in the report are mostly averages, medians, and proportions. I need to add grand total for benchmarking purposes in all of them. So basically I need to plot measures by the group and also plot the "ungrouped metric".

Example

[Lind25]

Hi

I can't access that file. A screenshot/mock up of your desired result would be most useful.

Based on what you are saying, using the ALLEXCEPT function or just applying ALL to some columns, rather than a whole table, should give you the desired result and allow the date slicers to be applied.

[Booth070]

I woiuld like something like this, where the column total represents all cases (A+B+C+...). At the moment I can only think of making two separate plots (but I find that is not helpful since I am benchmarking and want to be able to compare - therefore want same axis -) or do the calculated table that I mentioned.

As I said, it is very important to still be able to filter by the slicers I have selected, specially a date one.

Does this clarify? (Sorry, maybe I am not explaining muself as I should)

Thanks for your help

[v-xjiin-msft]

Hi @Booth070,

Still need a sample. You can share us your file with One Drive or Google Drive or Dropbox. They are all free.

Thanks,
Xi Jin.

[Booth070]

Hi, was having trouble with my acccount but should be able to access it now:

example pbix

[Lind25]

Hi
I might be misunderstanding your request but are you trying to plot a straight line for the grand total? If not an example screenshot of what you are trying to achieve would be helpful.

If it is a straight line then this is easy to achieve by creating a new measure that removes any filters applied to your existing measure.
Eg
Grand total measure = calculate( [existing measure], all(table1), all(table))