cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aggarwal25
Frequent Visitor

Sum column values based on another column

Hi All

 

I have some data and I want to sum 'Translated Ampount' based on 'revenue Category' column and then be able to map it with the customer. PLease let me know how can I do that. Apprecaite any help.2017-08-17_15-34-11.png

 

 Thanks

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @aggarwal25,

 

You can take a look at below formulas if it suitable for your requirement:

 

Calculate column:

 

Sum Group = CALCULATE(SUM('Table'[Translated Ampount]),FILTER(ALL('Table'),[Revenue Catagory]=EARLIER('Table'[Revenue Catagory])) )

 

 

Measure:

 

Sum group 2 =
var catagory=LASTNONBLANK('Table'[Revenue Catagory],[Revenue Catagory])
return
CALCULATE(SUM('Table'[Translated Ampount]),FILTER(ALL('Table'),[Revenue Catagory]=catagory))

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @aggarwal25,

 

You can take a look at below formulas if it suitable for your requirement:

 

Calculate column:

 

Sum Group = CALCULATE(SUM('Table'[Translated Ampount]),FILTER(ALL('Table'),[Revenue Catagory]=EARLIER('Table'[Revenue Catagory])) )

 

 

Measure:

 

Sum group 2 =
var catagory=LASTNONBLANK('Table'[Revenue Catagory],[Revenue Catagory])
return
CALCULATE(SUM('Table'[Translated Ampount]),FILTER(ALL('Table'),[Revenue Catagory]=catagory))

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Anonymous
Not applicable

@Hi @aggarwal2,

 

Is there a customer in this table or is there a related table that contains customer? If this is the case you can easily drag and drop customer, translated amount an revenue category onto the Power BI desktop canvas and it works. Also you could create several measures that sum the different revenue categories like:

 

Consulting Revenue =
SUMX (
    FILTER ( Table1; Table1[Revenue Category] = "Consulting" );
    Table1[Translated Amount]
)

If there is no customer table related to this table or no customer in this table, how would you assign a part of the Translated amount to a customer? So what is the distribution key?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors