forked from PowerBI-tips/TabularEditor-Scripts
/
Multi-Total Calc Group (base script).csx
73 lines (71 loc) · 2.67 KB
/
Multi-Total Calc Group (base script).csx
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
// '2023-07-09 / B.Agullo /
//
// Multi-Total Calc Group: Base script
//
// The development of this script is shown here https://www.esbrina-ba.com/industrializing-calculation-groups/
// store as macro and select only column as target object
// to use the right click on a single column you want to use to slice your data from the columns section of your matrix.
// to add custom totals to the matrix using this calc group as top level column field, you need to run
// the script shared in the file "Multi-Total Calc Group (add total script).csx" in this same repository folder.
//
// Follow Bernat on LinkedIn and Twitter
// https://www.linkedin.com/in/bernatagullo/
// https://twitter.com/AgulloBernat
#r "Microsoft.VisualBasic"
using Microsoft.VisualBasic;
if (Selected.Columns.Count() != 1)
{
Error("Select only 1 column and try again");
return;
}
Column column = Selected.Column;
string suggestedCalcGroupName = column.Name + " Multi-Totals";
string calcGroupName = Interaction.InputBox(
Prompt:"Please provide the name of the multi-total calc group.",
DefaultResponse:suggestedCalcGroupName);
if (calcGroupName == "")
{
Error("No name provided");
return;
};
CalculationGroupTable calcGroup =
Model.AddCalculationGroup(
calcGroupName);
string valuesCalcItemName = "Values";
string valuesCalcItemExpression =
String.Format(
@"IF(
ISINSCOPE( {0} ),
SELECTEDMEASURE()
)", column.DaxObjectFullName);
CalculationItem valuesCalcItem =
calcGroup.AddCalculationItem(
name: valuesCalcItemName,
expression: valuesCalcItemExpression);
valuesCalcItem.FormatDax();
valuesCalcItem.Description = "This calculation item is to show the breakdown by " + column.Name;
valuesCalcItem.Ordinal = 0;
string totalCalcItemName = "Total";
string totalCalcItemExpression =
String.Format(
@"IF(
NOT ISINSCOPE( {0} ),
SELECTEDMEASURE()
)", column.DaxObjectFullName);
CalculationItem totalCalcItem =
calcGroup.AddCalculationItem(
name: totalCalcItemName,
expression: totalCalcItemExpression);
totalCalcItem.FormatDax();
totalCalcItem.Description = "This calculation item is to show the regular total as a calculation item along with different totals that will be added to this calculation group";
totalCalcItem.Ordinal = 1;
string calcGroupTypeLabel = "CalcGroupType";
string calcGroupTypeValue = "MultiTotal";
calcGroup.SetAnnotation(
calcGroupTypeLabel,
calcGroupTypeValue);
string calcGroupValuesFieldLabel = "ValuesField";
string calcGroupValuesFieldValue = column.DaxObjectFullName;
calcGroup.SetAnnotation(
calcGroupValuesFieldLabel,
calcGroupValuesFieldValue);