Forum Discussion

JMcAnarney1's avatar
JMcAnarney1
Helper I
3 years ago

Question for Summing Column based off fields and dividing that into categories based off totals

How can I do following:   I want to sum the following columns to where it will display the same totals based off criteria for each row.  The reason I want to do this is because I want to make a formula to where it will display something like the following:

 

Issue is now I do a formula but it is summing each line item that makes up the aggregate but does not display the aggregate itself as below with the following formula: 

 

CALCULATE(SUMX(
    KEEPFILTERS(VALUES('Bid Template Data'[Bid Event])),
    CALCULATE(SUM('Bid Template Data'[Total Cost]),'Bid Template Data'[Bidder] = "Engineering Estimate",'Bid Template Data'[Winning Bid] ="No")))
 
How can I write this formula so that it would appear like the following as opposed to just the total cost column shifting over?

 

I want it like the picture above so i can eventually breakout and have panel to where I can click and it removes everything that is less than x amount,'

 

 

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    JMcAnarney1 , based on what I got. one of the two should work

     

    = SUMX(
    VALUES('Bid Template Data'[Bid Event]),
    CALCULATE(SUM('Bid Template Data'[Total Cost]),filter('Bid Template Data', 'Bid Template Data'[Bidder] = "Engineering Estimate",'Bid Template Data'[Winning Bid] ="No")))

    = CALCULATE(SUMX(
    VALUES('Bid Template Data'[Bid Event]),
    CALCULATE(SUM('Bid Template Data'[Total Cost]),filter('Bid Template Data', 'Bid Template Data'[Bidder] = "Engineering Estimate",'Bid Template Data'[Winning Bid] ="No"))), allselected())