Forum Discussion
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 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!
7 Replies
Lind25Resolver I
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))
v-xjiin-msftSolution Sage
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.
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".
- 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.
