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
Roseventura
Responsive Resident
Responsive Resident

To be a measure or not to be a measure (column)? That is the Question!

Hello BI Community.

 

I have a need to create either a measure or a new column which will be based off a numeric field and a measure.

 

The numeric field is:   Ext Sales Amt Shipped and is Data Type:  Decimal Number

 

The measure is:   Book-Ship Mo = calculate(sum(PBI_Book_Ship_Multi[Ext Sales Amt Shipped]),filter(PBI_Book_Ship_Multi,PBI_Book_Ship_Multi[Book Ship Mo Compare]="Y"))

 

What I need is a percentage calculation, but I'm not sure if it should be a measure or a new column.  It would very basically

be this:  Book-Ship Mo / Ext Sales Amt Shipped * 100

 

I tried both measure and column and I either get NaN or numbers that are way off from what they need to be.

 

Can someone help?

Rose

 

 

 

 

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

@Roseventura,

 

You may add a measure as shown below. Besides, a calculated column is computed during the database processing and then stored in the model, while a measures is evaluated at query time and does not consume memory and disk space.

Percent =
CALCULATE (
    SUM ( PBI_Book_Ship_Multi[Ext Sales Amt Shipped] ),
    FILTER ( PBI_Book_Ship_Multi, PBI_Book_Ship_Multi[Book Ship Mo Compare] = "Y" )
)
    / SUM ( PBI_Book_Ship_Multi[Ext Sales Amt Shipped] )
    * 100
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-chuncz-msft
Community Support
Community Support

@Roseventura,

 

You may add a measure as shown below. Besides, a calculated column is computed during the database processing and then stored in the model, while a measures is evaluated at query time and does not consume memory and disk space.

Percent =
CALCULATE (
    SUM ( PBI_Book_Ship_Multi[Ext Sales Amt Shipped] ),
    FILTER ( PBI_Book_Ship_Multi, PBI_Book_Ship_Multi[Book Ship Mo Compare] = "Y" )
)
    / SUM ( PBI_Book_Ship_Multi[Ext Sales Amt Shipped] )
    * 100
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you.  That worked!

 

Rose

 

Hi there,

 

Glad to help. 99.999% of all column calculations can also be done as a measure. We have a great article at PowerPivotPro which talks about when to use one vs another. Couple of questions first. So if the sum of Book-Ship Mo was (800) and the sum of Ext Sales Amount Shipped was (1,000). You'd want your percentage calculation to be 80 (.8 * 100). Is that correct?


Reid Havens - Principal Consultant

PowerPivotPro

Yes, that is correct.

 

(I'll check out that article you referred me to, thanks.)

 

Rose

 

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.