Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
9 months ago
Solved

Cumulative per customer

Nice day I have a table called LoadItems. This table has these fields: - FEC Dispatch related to Dim_Calendar DAX with the Date field; it is a relationship that is kept but not active because it g...
  • Zanqueta's avatar
    Zanqueta
    9 months ago

    Hello Syndicate_Admin, let me try.


    To achieve this, you need to create DAX measures that accumulate the M2 Dispatched values per Client and Year, ordered from highest to lowest, and then calculate the cumulative percentage.

     

    Suggested DAX Measures:

    Total M2 Dispatched per Client and Year
    TotalM2PerClientYear =
CALCULATE(
    SUM(LoadItems[M2 Dispatched]),
    ALLEXCEPT(LoadItems, LoadItems[Client], LoadItems[Year])
)

    Accumulated M2 Dispatched for Pareto

     

    AccumulatedM2Pareto =
VAR CurrentClient = SELECTEDVALUE(LoadItems[Client])
VAR CurrentYear = SELECTEDVALUE(LoadItems[Year])
RETURN
CALCULATE(
    SUM(LoadItems[M2 Dispatched]),
    FILTER(
        ALL(LoadItems),
        LoadItems[Year] = CurrentYear &&
        LoadItems[M2 Dispatched] >= 
            CALCULATE(SUM(LoadItems[M2 Dispatched]), LoadItems[Client] = CurrentClient)
    )
)

    Cumulative Percentage

     

    CumulativePercentage =
DIVIDE(
    [AccumulatedM2Pareto],
    CALCULATE(SUM(LoadItems[M2 Dispatched]), ALL(LoadItems), LoadItems[Year] = SELECTEDVALUE(LoadItems[Year]))
)

     

Ahmed-Elfeel's avatar
Ahmed-Elfeel
Super User
9 months ago

Hi Syndicate_Admin,

I hope you are doing well today ☺️

Ok we will Create a measure that calculates running totals of M2 Dispatched by client within each year using DAX so here is the DAX solution:

Cumulative M2 Dispatched = 
VAR CurrentYear = SELECTEDVALUE('Dim_Calendar'[Year])
VAR CurrentClient = SELECTEDVALUE(LoadItems[Client])
VAR SummaryTable =
    SUMMARIZE(
        FILTER(ALLSELECTED(LoadItems), 
               RELATED('Dim_Calendar'[Year]) = CurrentYear),
        LoadItems[Client],
        "TotalM2", SUM(LoadItems[M2 Dispatched])
    )
VAR SortedTable =
    ADDCOLUMNS(
        SummaryTable,
        "Rank", RANKX(SummaryTable, [TotalM2],, DESC)
    )
VAR CurrentRank =
    MAXX(
        FILTER(SortedTable, LoadItems[Client] = CurrentClient),
        [Rank]
    )
RETURN
    SUMX(
        FILTER(SortedTable, [Rank] <= CurrentRank),
        [TotalM2]
    )

 

Also here is alternative and simple approach:

Cumulative M2 Dispatched = 
VAR CurrentYear = SELECTEDVALUE('Dim_Calendar'[Year])
VAR CurrentClientM2 = 
    CALCULATE(
        SUM(LoadItems[M2 Dispatched]),
        USERELATIONSHIP(LoadItems[FEC Dispatch], 'Dim_Calendar'[Date])
    )
VAR AllClientsCurrentYear =
    CALCULATETABLE(
        SUMMARIZE(
            LoadItems,
            LoadItems[Client],
            "ClientM2", SUM(LoadItems[M2 Dispatched])
        ),
        USERELATIONSHIP(LoadItems[FEC Dispatch], 'Dim_Calendar'[Date]),
        'Dim_Calendar'[Year] = CurrentYear
    )
RETURN
    CurrentClientM2 + 
    SUMX(
        FILTER(
            AllClientsCurrentYear,
            [ClientM2] > CurrentClientM2
        ),
        [ClientM2]
    )

 

To use this in your Pareto analysis:

  • Create the cumulative measure above

  • Add it to your matrix visual

  • Create a Pareto line by adding a line chart with:

    • X axis: Client (sorted by M2 Dispatched descending)
    • Y axis: Your new cumulative measure
    • Use the secondary Y axis for percentage

For the percentage version:

Cumulative % = 
DIVIDE(
    [Cumulative M2 Dispatched],
    CALCULATE(
        SUM(LoadItems[M2 Dispatched]),
        ALLSELECTED(LoadItems[Client]),
        USERELATIONSHIP(LoadItems[FEC Dispatch], 'Dim_Calendar'[Date])
    )
)

 

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    9 months ago

    I applied both measures:

    in the first, the result is empty.

    In the second, they do not accumulate

    I appreciate your collaboration

  • Zanqueta's avatar
    Zanqueta
    Super User
    9 months ago

    Hello Syndicate_Admin, let me try.


    To achieve this, you need to create DAX measures that accumulate the M2 Dispatched values per Client and Year, ordered from highest to lowest, and then calculate the cumulative percentage.

     

    Suggested DAX Measures:

    Total M2 Dispatched per Client and Year
    TotalM2PerClientYear =
CALCULATE(
    SUM(LoadItems[M2 Dispatched]),
    ALLEXCEPT(LoadItems, LoadItems[Client], LoadItems[Year])
)

    Accumulated M2 Dispatched for Pareto

     

    AccumulatedM2Pareto =
VAR CurrentClient = SELECTEDVALUE(LoadItems[Client])
VAR CurrentYear = SELECTEDVALUE(LoadItems[Year])
RETURN
CALCULATE(
    SUM(LoadItems[M2 Dispatched]),
    FILTER(
        ALL(LoadItems),
        LoadItems[Year] = CurrentYear &&
        LoadItems[M2 Dispatched] >= 
            CALCULATE(SUM(LoadItems[M2 Dispatched]), LoadItems[Client] = CurrentClient)
    )
)

    Cumulative Percentage

     

    CumulativePercentage =
DIVIDE(
    [AccumulatedM2Pareto],
    CALCULATE(SUM(LoadItems[M2 Dispatched]), ALL(LoadItems), LoadItems[Year] = SELECTEDVALUE(LoadItems[Year]))
)