Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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".
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.
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
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.
Hi, was having trouble with my acccount but should be able to access it now:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
80 | |
67 |