Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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
Yes, that is correct.
(I'll check out that article you referred me to, thanks.)
Rose
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |