Calculated Table vs. Measure

DAX DAX Calculations
1

Hello Experts,

@deltaselect

I have done calculation by Calculated table but I was wondering if I can achieve the same with measures? Is it possible?

Here is the problem statement:

I have an excel file as a data source. And I want to:

Summarize : HandQuantity (Sum and Average)
Per Day
Per product description
Per order number.

I am able to do it with the help of Calculated Table.

Calculated Table = 
VAR Calc = 
SUMMARIZECOLUMNS(
  'Sheet1'[ManufactureDate].[Date],
  Sheet1[ProductDescription],
  Sheet1[OrderID],
  "SumHandQuantity", CALCULATE(SUM(Sheet1[HandAddQuantity])),
  "AvgHandQuantity", CALCULATE(AVERAGE(Sheet1[HandAddQuantity]))
)
  Return  Calc

`

https://drive.google.com/file/d/1GxsESok1h-nLtdpqmt3bELi4fuVzvlbt/view?usp=sharing

I think by using Calculated table, I am cheating. Attached above is the file.

Thanks in advance,
-RK

2

Hi @rit372002 ,

As far as I can see, in this example, basic measures works fine, and are better to use as a calculated table, as you are only using one dimension QTY.

Sum HandAddQty = SUM( Sheet1[HandAddQuantity])
Average HandQty = AVERAGE( Sheet1[HandAddQuantity])

For your example report it is sufficient to use SUM and Average, as the data is reported from only one column “HandAddQuantity”, then AverageX and SumX are not needed.

With the measures above, I was able to reproduce your report.
See also attached PBIX : CalculatedTable NewReqv2.pbix (2.0 MB)

image

Hope this answers your question,
Kind regards, JW

3

Thanks! Didn’t realized it’s going to be that easy. That bags just one more question : If I had to add one more column you mentioned I needed Sumx Or Averagex. Can you enlighten me on that? Say, if I had to add “Quantity” for sum and average then I needed SumX or AverageX?

Thanks again for help,
-RK

4

Hi @rit372002 ,

SumX and AverageX are (table-)row calculations, and are to be used for example to calculate costs or sales using a combination of QTY and Price, see below.
image

You may look attached video for a more detailed explanation.

In case you should not have access to the video, please let me know.

Kind regards, JW

P.S. I was content this time your question was easy :smiley:

5

Thanks a lot @deltaselect That helped. I will mark that as a solution.