Ideas for Measure

[Julia2023]

Hi everyone, I'm looking for some ideas. I have a measure called "Position," and I need to create a measure called "GOAL" (which should be the sum of "Position" but with a total of 100%). The current approach I tried involves adding the Product ID level, which is causing heavy loading and resulting in an error: "The operation was canceled due to locking conflicts."

Position = 

VAR TotalRev = 
    CALCULATE(
        [revenue],
        REMOVEFILTERS(ID[Product ID])
    )

VAR Result = 
    DIVIDE([revenue], TotalRev, 0)

RETURN
    Result

GOAL = 
VAR IDSales = [Position]

RETURN
    IF(
        ISBLANK(IDSales),
        BLANK(),
        VAR Table = 
            ADDCOLUMNS(
                ALL(ID[Product ID]),
                "@Position",
                [Position]
            )
        
        VAR FilteredTable = 
            FILTER(
               Table,
                [@Position] >= IDSales
            )
        
        VAR Result = 
            SUMX(
                FilteredTable,
                [@Position]
            )

        RETURN
            Result
    )

[v-heq-msft]

Hi @Julia2023 ,
You can calculate the total sum of the "Position" measure. And then Divide each "Position" value by the total sum to get the percentage.

TotalPosition = 
    CALCULATE(
        SUM('YourTable'[Position]),
        REMOVEFILTERS('YourTable')
    )

GOAL = 
DIVIDE(
    SUM('YourTable'[Position]),
    TotalPosition,
    0
)

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[shafiz_p]

Hi @Julia2023  What I understood about your Goal measure in your provided image, that it is calculating cumulative sum of position based on product ID.

If this is the case, then you should rank your position decending order and evaluate cumulative sum. You can try below 2 measure.

Rank Measure:

ProductKeyRank = 
RANKX(
    ALLSELECTED(DimProduct[ProductKey]),
    [Position],
    ,
    DESC,
    DENSE
)

We will filter product table based on this rank and feed this table to calculate function to sum up position to construct 100%.

Goal Measure:

Goal = 
VAR CurrentRank = [ProductKeyRank]

RETURN
CALCULATE(
    [Position],
    FILTER(
        ALLSELECTED(DimProduct[ProductKey]),
        [ProductKeyRank] <= CurrentRank
    )
)

Change column name according to your table column name. You could rank any dimension based on this technique and conduct cumulative sum.

Here is the output of my goal measure:

You can avoid total level by using inscope or hasonevalue function.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[Julia2023]

Thank you for the idea. It works when using a filter, but without any filter, loading and nothing... maybe it's because have more than 180k product IDs

[danextian]

Hi @Julia2023 

This measure below creates  a virtual summary table by product id containing a column called @position which returns a value if [goal] is equal to 1.

SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'table', 'table'[product id] ),
        "@position", IF ( [goal] = 1, [position] )
    ),
    [@position]
)