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, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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