Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Henrik_PBI
Helper II
Helper II

Cumulative total on categories (no date dimension)

Hello,

 

I'm in need for a measure that shows me the cumulative total in a table. The table consist of a category column ("Reason") and a amount column ("Amount"). I am creating a pareto-chart and therefore I need to create a measure that shows me the cumulative total.

 

An illustration of what I want. I have the Reason and Amount columns, and want to create the column marked in red: 

Forum1.png

 

I have tried this measure, but I don't get the correct numbers from it:
note:  "Total_amount" = SUM(events(amount))

Cumulative_total = 
VAR Cumulative_test = [Total_amount]

RETURN
   SUMX(
    FILTER(
        summarize(ALLSELECTED(events);events[Reason];
        "Test";[Total_amount]);
            events[amount] >= Cumulative_test);
            events[amount]
   )

 

Could anybody help me on this?

 

1 ACCEPTED SOLUTION
Henrik_PBI
Helper II
Helper II

Hi,

Thank you for your replies, @amitchandak and @v-jingzhang

 

I found a solution to this using this formula (a lot of similarities with the formulas you recommended):

reason Running Total = 
VAR ReasonRank = 
    RANKX(
        ALL(events[Reason]);
        [Total_amount];;
        DESC
    )
VAR RunningTotal = 
    CALCULATE(
        [Total_amount];
        FILTER(
            ALL(events[Reason]);
            ReasonRank >= RANKX(
                            ALL(events[Reason]);
                            [Total_amount];;
                            DESC;
                            Dense)
            )
        )
RETURN 
      RunningTotal
    

 

View solution in original post

8 REPLIES 8
Henrik_PBI
Helper II
Helper II

Hi,

Thank you for your replies, @amitchandak and @v-jingzhang

 

I found a solution to this using this formula (a lot of similarities with the formulas you recommended):

reason Running Total = 
VAR ReasonRank = 
    RANKX(
        ALL(events[Reason]);
        [Total_amount];;
        DESC
    )
VAR RunningTotal = 
    CALCULATE(
        [Total_amount];
        FILTER(
            ALL(events[Reason]);
            ReasonRank >= RANKX(
                            ALL(events[Reason]);
                            [Total_amount];;
                            DESC;
                            Dense)
            )
        )
RETURN 
      RunningTotal
    

 

v-jingzhang
Community Support
Community Support

Hi @Henrik_PBI 

Since your data is sorted by total amount descendingly, you can add a rank column to sort it and use this column in the cumulative total measure. Here is the PBIX file.

Create a summarized table:

Table2 = SUMMARIZE(Table1,Table1[relatertÅrsak],"total",SUM(Table1[amount]))

Add a rank column

rankIndex = RANKX(Table2,Table2[total])

Then create a measure

Cumulative total = SUMX(FILTER(ALL(Table2),Table2[rankIndex]<=MAX(Table2[rankIndex])),Table2[total])

022502.jpg

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

amitchandak
Super User
Super User

@Henrik_PBI , Try like

Cumulative_total =
SUMX(
ADDCOLUMNS(
summarize(events;events[Reason];
"Test";[Total_amount]);
"Cumm", calculate(sum([Test]), filter(events, [Test] <= max( [Test]))));
[Cumm]
)

 

or

 

Cumulative_total =
SUMX(
FILTER(
summarize(allselected(events);events[Reason];
"Test";[Total_amount]);
[Test] <= max([Test]));
[Test]
)

Hi, @amitchandak - thank you for your reply.

 

I've tested your suggestion but I don't seem to get it right.

 

My formula:

Cumulative_total = 
   SUMX(
    FILTER(
        summarize(ALLSELECTED(events);events[Reason];
        "Test";[Total_amount]);
            [Test] <= [Test]);
            [Test]
   )

 

My output:

(the real reason names are hidden)

Forum 2.png

 

Any ideas on what I am doing wrong?

 

@Henrik_PBI , Try like

 

Cumulative_total =
SUMX(
ADDCOLUMNS(
summarize(events;events[Reason];
"Test";[Total_amount]);
"Cumm", calculate(sum([Test]), filter(events, [Test] <= max( [Test]))));
[Cumm]
)

 

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Hi, @amitchandak 

I get an error when trying to write your formula:

forum 3.png

 

How do I share an pbix file here?

@Henrik_PBI , put it like [Test]

 

You can upload to dropbox or onedrive and share link

@amitchandak, Thank you, I really appreciate your help.

 

I still get an error when writing your formula:

Forum 4.png

 

Link to sample pbix file:
https://www.dropbox.com/s/y6ti27rk50i3h4w/SampleData_PBI.pbix?dl=0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.