cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SajjadMousavi
Helper II
Helper II

Use SUMMARIZE in measure calculations

Hi all.

 

I have created a calculated table as follows:

 

CompanyMSSummarized = SUMMARIZE(FILTER(DistributionPerCompany, DistributionPerCompany[ProdType] = "Disrtribution"), DistributionPerCompany[Year], DistributionPerCompany[Company], "Qty", SUM(DistributionPerCompany[RealQty]))
 
This table summarizes another table which has many columns, from which only the above 3 are needed. Then I have added these calculated columns to CompanyMSSummarized:
 
CurrentPct =
VAR TotalQty = CALCULATE(SUM(CompanyMSSummarized[Qty]), FILTER(ALL(CompanyMSSummarized), (CompanyMSSummarized[Year] = EARLIER(CompanyMSSummarized[Year]))))
RETURN 100 * CompanyMSSummarized[Qty] / TotalQty
 
PrevPct = CALCULATE(SUM(CompanyMSSummarized[CurrentPct]), FILTER(ALL(CompanyMSSummarized), (CompanyMSSummarized[Company] = EARLIER(CompanyMSSummarized[Company])) && (CompanyMSSummarized[Year] = EARLIER(CompanyMSSummarized[Year]) - 1)))
 
Now, I want to know if there is any way to re-write these columns as measures. I need this because measures have some formatting options which can be used in some visuals (such as KPIs), which are not available directly through those visuals. As these fields are not calculated very often (Once a month), using measures and in-memory calculations is not resource-intensive compared to calculated tables.
 
Thanks in advance
1 ACCEPTED SOLUTION

@SajjadMousavi 

Not sure what are you trying to achieve, but you may try

CurrentPct =
VAR CurrentYear =
    MAX ( DistributionPerCompany[Year] )
VAR CurrentCompany =
    MAX ( DistributionPerCompany[Company] )
VAR CompanyMSS =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( DistributionPerCompany ),
            DistributionPerCompany[ProdType] = "Distribution"
                && DistributionPerCompany[Year] = CurrentYear
        ),
        DistributionPerCompany[Year],
        DistributionPerCompany[Company],
        "Qty", SUM ( DistributionPerCompany[RealQty] )
    )
VAR TotalQty =
    SUMX ( CompanyMSS, [Qty] )
VAR Qty =
    SUMX (
        FILTER ( CompanyMSS, DistributionPerCompany[Company] = CurrentCompany ),
        [Qty]
    )
RETURN
    100 * Qty / TotalQty

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

Hi @SajjadMousavi 
Almost the same

CurrentPct =
VAR TotalQty =
    CALCULATE (
        SUM ( CompanyMSSummarized[Qty] ),
        FILTER (
            ALL ( CompanyMSSummarized ),
            CompanyMSSummarized[Year] = EARLIER ( CompanyMSSummarized[Year] )
        )
    )
RETURN
    100 * SUM ( CompanyMSSummarized[Qty] ) / TotalQty
PrevPct =
CALCULATE (
    SUM ( CompanyMSSummarized[CurrentPct] ),
    FILTER (
        ALL ( CompanyMSSummarized ),
        CompanyMSSummarized[Company] = EARLIER ( CompanyMSSummarized[Company] )
            && CompanyMSSummarized[Year]
                = EARLIER ( CompanyMSSummarized[Year] ) - 1
    )
)

Thansk for your reply, but I'm looking for a way to skip creation of summarized table. In addition, you cannot use EARLIER function in a measure, as measres are not aware of row context.

@SajjadMousavi 
There is a row context created by FILTER. However, please clarify exactly what is required. I'm a little confused. Thank you

I just want to skip creation of CompanyMSSummarized table in data model (want to create it on-the-fly in some way) and do calculations. But when I summarize DistributionPerCompany in a measure, I cannot use it in subsequent calculations (like the one you suggested) and Power BI gives this error:

 

SajjadMousavi_1-1652501126352.png

 

 

Hi @SajjadMousavi 

 

1. You absolutely can create a table on the fly, store it in a var and USE it subsequently. The only thing you're missing in your code is the correct references to the columns of the table. You cannot use the name of the variable to refer to them. For instance, you have to use SUMX and the original names of the columns. It's all about DATA LINEAGE.

2. You should never use SUMMARIZE to perform calculations in its body since SUMMARIZE is a very buggy (and complex) function (very dangerous in measures), and MS have refused fixing it a long time ago. Instead, you should use the hybrid SUMMARIZE/ADDCOLUMNS. Use SUMMARIZE only to get the distinct combination of rows from a (extended) table.

3. You should also never filter a full table (row 7 in your code). Always only the minimum number of columns that are needed.

4. EARLIER will not work in your code because to use it you have to have 2 different row contexts active at the same time. One is not enough. Incidentally, you should never use this function. Instead, you should use variables.

 

Best

Thanks a lot for details. About 3rd point you mentioned, this table has exactly minimum number of columns needed for calculations. Summarization is just to sum up data per year (as can be seend from code). Anyway, I will try to re-write the code using guidelines you gave. Hope I can do it.

@SajjadMousavi 

Please paste the so I can copy it and edit 

Thanks in advance for your time:

 

CurrentPct = 
VAR CompanyMSS = SUMMARIZE(FILTER(DistributionPerCompany, DistributionPerCompany[ProdType] = "Distribution"), DistributionPerCompany[Year], DistributionPerCompany[Company], "Qty", SUM(DistributionPerCompany[RealQty]))
VAR TotalQty =
    CALCULATE (
        SUM ( CompanyMSS[Qty] ),
        FILTER (
            ALL ( CompanyMSS ),
            CompanyMSS[Year] = EARLIER ( CompanyMSS[Year] )
        )
    )
RETURN
    100 * SUM ( CompanyMSS[Qty] ) / TotalQty

 

@SajjadMousavi 

Not sure what are you trying to achieve, but you may try

CurrentPct =
VAR CurrentYear =
    MAX ( DistributionPerCompany[Year] )
VAR CurrentCompany =
    MAX ( DistributionPerCompany[Company] )
VAR CompanyMSS =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( DistributionPerCompany ),
            DistributionPerCompany[ProdType] = "Distribution"
                && DistributionPerCompany[Year] = CurrentYear
        ),
        DistributionPerCompany[Year],
        DistributionPerCompany[Company],
        "Qty", SUM ( DistributionPerCompany[RealQty] )
    )
VAR TotalQty =
    SUMX ( CompanyMSS, [Qty] )
VAR Qty =
    SUMX (
        FILTER ( CompanyMSS, DistributionPerCompany[Company] = CurrentCompany ),
        [Qty]
    )
RETURN
    100 * Qty / TotalQty

Thanks a lot. I was so busy that I couldn't check here. Will test your solution.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors