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 generates inconsistency.- M2 Dispatched , which is a calculated column.- Client , which is the company name.I put together the matrix that I attach, ordered by M2 Dispatched. The % field is the same M2 Dispatched field but viewed as the % of the column.I need the size that accumulates the M2 Dispatched, per client, per year, to be able to put together the paretto. I can't do it, can someone help me? Thank you very much

Cumulative per customer | Microsoft Fabric Community

7 Replies

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
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

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]))
)

Ashish_Mathur
Super User
9 months ago
Hi,

Share the download link of the PowerBI file.

Praful_Potphode

Super User

9 months ago

HI Syndicate_Admin ,

try below measure:

Cumulative M2 Dispatched = 
VAR CurrentYear = SELECTEDVALUE('Dim_Calendar'[Year])
VAR CurrentClient = SELECTEDVALUE(LoadItems[Client])

// Get current client's M2 value for the selected year
VAR CurrentM2 = 
    CALCULATE(
        SUM(LoadItems[M2 Dispatched]),
        USERELATIONSHIP(LoadItems[FEC Dispatch], 'Dim_Calendar'[Date]),
        'Dim_Calendar'[Year] = CurrentYear
    )

// Calculate cumulative sum
VAR CumulativeSum = 
    CALCULATE(
        SUM(LoadItems[M2 Dispatched]),
        FILTER(
            ALLSELECTED(LoadItems[Client]),
            VAR ClientM2 = 
                CALCULATE(
                    SUM(LoadItems[M2 Dispatched]),
                    USERELATIONSHIP(LoadItems[FEC Dispatch], 'Dim_Calendar'[Date]),
                    'Dim_Calendar'[Year] = CurrentYear
                )
            RETURN ClientM2 >= CurrentM2
        ),
        USERELATIONSHIP(LoadItems[FEC Dispatch], 'Dim_Calendar'[Date]),
        'Dim_Calendar'[Year] = CurrentYear
    )

RETURN
CumulativeSum

Cumulative % = 
VAR CurrentYear = SELECTEDVALUE('Dim_Calendar'[Year])
VAR Cumulative = [Cumulative M2 Dispatched]

VAR TotalYear = 
    CALCULATE(
        SUM(LoadItems[M2 Dispatched]),
        ALLSELECTED(LoadItems[Client]),
        USERELATIONSHIP(LoadItems[FEC Dispatch], 'Dim_Calendar'[Date]),
        'Dim_Calendar'[Year] = CurrentYear
    )

RETURN DIVIDE(Cumulative, TotalYear, 0)

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

v-ssriganesh
Community Support
9 months ago
Hi Syndicate_Admin,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to Praful_Potphode, Zanqueta & Ahmed-Elfeel for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.
v-ssriganesh
Community Support
9 months ago
Hello Syndicate_Admin,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

Forum Discussion

Cumulative per customer

Suggested DAX Measures:

7 Replies

Suggested DAX Measures:

Related content

Cumulative Total

Creating Cumulative Graphs

Year to date cumulative

Cumulative Total

cumulative sum