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
Anonymous
Not applicable

Summing a total (referencing a measure)

Hello  -  The result of this measure is shown below.     What I would like to do is to sum up the net price....but use the the values for the sum.   For example, sum up the total of all "Unforecasted - Ordered".  

 

Tried to create a sum formula, but got an error saying I could not sum anything since Measure 11 is in a table with no columns.   Is there a way to do this?   

 

Measure 11 =
IF (
   [Measure1]<> BLANK(),
   "Forecasted - Ordered",
       "Unforecasted-Ordered"
   )

 

 

measure sum.png

7 REPLIES 7
DataZoe
Employee
Employee

You could try and put it back in a table context:

 

Measure 11 =
IF (
   countrows(filter('TableNameWhereDataIs',[Measure1]<>blank())>0,
   "Forecasted - Ordered",
       "Unforecasted-Ordered"
   )

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Anonymous
Not applicable

I tried this, but got a red line under the part starting with filter......and error saying too many arguments passed to the countrows function

 

Measure 11 table =
IF (
   countrows(filter(Sales_Orders_for_Charts,[Measure1]<>blank())>0,
   "Forecasted - Ordered",
       "Unforecasted-Ordered"
   ))

@Anonymous I may have my ()'s mixed a little, try this:

 

Measure 11 table =
IF (
    COUNTROWS ( FILTER ( Sales_Orders_for_Charts, [Measure1] <> BLANK () ) ) > 0,
    "Forecasted - Ordered",
    "Unforecasted-Ordered"
)

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Anonymous
Not applicable

Hi DataZoe  

 

Maybe I was not clear in my original post.   

 

I was able to get the sum of the total of that measure.   

 

What I am trying to do is to be able to filter by each value, independently.    In other words, sum up all of the "Unforecasted-Ordered".      And sum up all of the "Forecasted-Ordered".   Each independently so I could use each of those sums in a card visual, or maybe put the sums in a bar chart side by side.       Does that make sense?    Sort of if the Measure 11 was a calculated column, instead of a measure.   If it was a column I would be able to filter the rows and add up each row, based on which value was in that row  (Unforecasted...or Forecasted).     But because this is in a measure, I'm not able to do that...which is the issue I am trying to solve.    Hope that is more clear.  

@Anonymous Oh I did misunderstand.  

 

You could try this, one for each option:

 

Unforecasted-Ordered Net Price =
CALCULATE (
    [Net Price],
    FILTER ( Sales_Orders_for_Charts, [Measure 11] = "Unforecasted-Ordered" )
)

 

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Anonymous
Not applicable

Hi DataZoe   -    Just to clarify: 

 

1. Does this mean I would use the measure below to create a new table  (so add new table)?  

 

2.   The "table where data is".    Does this refer to the net price data  (which would my Orders table).    Or does it refer to where Measure 11 is  (which is in a special group that contains only measures, no columns).  

 

Thanks for your help so far!

@Anonymous I mean the Orders table 🙂

 

Measure 11 =
IF (
   countrows(filter('Orders',[Measure1]<>blank())>0,
   "Forecasted - Ordered",
       "Unforecasted-Ordered"
   )

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

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.