Forum Discussion

Booth070's avatar
Booth070
Frequent Visitor
8 years ago

Temporary custom tables? (Plot grand total)

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 ...
Need Help
v-xjiin-msft's avatar
v-xjiin-msft
Icon for Solution Sage rankSolution Sage
8 years ago

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's avatar
Booth070
Frequent Visitor
8 years ago

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's avatar
    Lind25
    Icon for Resolver I rankResolver I
    8 years ago

    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's avatar
      Booth070
      Frequent Visitor
      8 years ago

      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's avatar
        v-xjiin-msft
        Icon for Solution Sage rankSolution Sage
        8 years ago

        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.