ssas tabular - measure design

Question

question about measures in ssas tabular. Is it better to have the physical measure in the fact table as a column and then do a simple sum measure? e.g

Imagine the scenario I have a measure called Income in the fact table, but the user wants to see ProductA income, Product B income as individual measures (not using income measure with product dimension, which yes gives the same result)

Or is it better to do a dax calculation with a sum and filter based on the product dim. e.g. Product B Income:= CALCULATE(SUM('fact'[Income]);VALUES('Product Type dim');('Product Type dim'[Product Tye] ="ProductB"))

I have tried both methods and both return the same result... just want to know what would be best practice here. (fact table around 300million rows)

Answer 1

The fewer the measures the more performant your report will be in the end. I would recommend, in this situation, just sticking to a simple sum() measure and applying it each product. This would be the most efficient approach, doing a calculation on 300 million rows whilst filtering and invoking Values will definitely slow you down.

Answer 2

DC07 How are end users viewing your model? Are they using it in PowerPivot (excel) or PowerBI? I would suggest performing those calculations in either the visualization element (PowerBI) in PowerPivot as a method of displaying the calculation.