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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bo_wang5
Helper II
Helper II

Summing by 2 category but total not showing

Hello All,

 

I'm trying to create a sum based on 2 different category measures.  "Forecast Status" and "Status" and it's not showing me the total.  Please help, Thanks in advance for any assistance.

 

In forecast and won =
if([Forecast Status]="in forecast",If([Status]="won",[Sum Won Q3],0))

 

bo_wang5_0-1638336419690.png

 

3 REPLIES 3
VahidDM
Super User
Super User

Hi @bo_wang5 

 

Is [Sum Won Q3] a measure? 

BTWTry this:

In forecast and won = SUMX(filter(Data,Data[Forecast Status]="in forecast"&&Data[Status]="won"),[Sum Won Q3])

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Thank you so much for responding.

 

Yes that is correct [Sum Won Q3] is a measure, it is calcuated as

 

Sum Won Q3=CALCULATE(sum(data[ASO: Amount (converted)]),filter(data,Data[Data Entry or Type]="Won-Q3"))
 
where data[ASO: Amount (converted)] and Data[Data Entry or Type] are physical columns. 
 
I tried your code that you suggested but it did not work.  both the "Forecast Status" and the "Status" are measures as well. here is my code for "Status" which uses the [Sum Won Q3] measure
 
Status =
if(hasonevalue(Data[Reference]),
IF ( [Sum Won Q3] <= 0, "Not Won", "won" )
)
 
bo_wang5_0-1638341235858.png

 

bo_wang5
Helper II
Helper II

Slight update to the formula, I just need to know how I can sum up the total of the entire column.  Once I have that I can use it for additional calculations. Thanks.
 
In forecast and won = if([Forecast Status]="in forecast",if([Status]="won",[Sum Won Q3],0),if(HASONEVALUE(Data[Reference]),0,"need formula to capture total"))
 

bo_wang5_0-1638338510519.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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