PowerPivot - Custom Calculation on summarized table

Question

I have a summarized table with two variables:

SUMMARIZE(Table;
             Table[Static1];
             Table[Static2];
             Table[Static3];
             "Total1Summarized"; MAX(Table[TOTAL1]);
             "Total2Summarized"; MAX(Table[TOTAL2])
                       )

I want to make a new variable SUM(Total1Summarized)/SUM(Total2Summarized)

I know I can do this task by creating two summed variables and dividing one of another, but I think having one summarize is quicker for large calculations.

My current solution:

Creating two different variables:

Variable1:=
SUMX(
     SUMMARIZE(Table;
               Table[Static1];
               Table[Static2];
               Table[Static3];
               "Total1Summarized"; MAX(Table[TOTAL1])
               );
     [Total1Summarized]
    )

Variable2:=
SUMX(
     SUMMARIZE(Table;
               Table[Static1];
               Table[Static2];
               Table[Static3];
               "Total2Summarized"; MAX(Table[TOTAL2])
               );
     [Total1Summarized]
    )

Dividing SUM of them:

FinalVariable:=SUM([Variable1])/SUM([Variable2])

Thank you in advance for answers!

score 1 · Accepted Answer · answered Oct 28 '15 at 15:43

I think you're confusing measures (1, 2) and variables (3, 4) which are both distinct concepts in DAX. It looks like you actually want to create a measure. If this is incorrect, please let me know, and I will edit this answer to be more appropriate for you.

What you're doing right now is pretty much it in terms of measures. Creating a single SUMMARIZE() is easy, as you show in your first code sample. That being said, there's to get the sum of two projected columns in a SUMMARIZE and divide them. If you just want a single measure, you can just divide the SUMX()s directly:

SingleMeasure:=
SUMX(
    SUMMARIZE(
        Table;
        Table[Static1];
        Table[Static2];
        Table[Static3];
        "Total1Summarized"; MAX(Table[TOTAL1])
    );
    [Total1Summarized]
) / SUMX(
        SUMMARIZE(
            Table;
            Table[Static1];
            Table[Static2];
            Table[Static3];
            "Total2Summarized"; MAX(Table[TOTAL2])
        );
        [Total2Summarized]
    )

You're not going to eliminate the dual SUMX(), though.

If you want to use variables, you could define your SUMMARIZE() as a variable and then simplify your SUMX()s:

SingleMeasure:=
VAR sumtable = 
    SUMMARIZE(
        Table;
        Table[Static1];
        Table[Static2];
        Table[Static3];
        "Total1Summarized"; MAX(Table[TOTAL1])
        "Total2Summarized"; MAX(Table[TOTAL2])
    );
return
SUMX(
    sumtable;
    [Total1Summarized]
) / SUMX(
        sumtable;
        [Total2Summarized]
    )

Sorry for inconvenience, I meant measures. As far as I understand, I cannot use _VAR_ in PowerPivot and I do not have PowerQuery. Thank you for clarifying. — dominic, Nov 17 '15 at 12:06

PowerPivot - Custom Calculation on summarized table

1 Answers1