topic Re: Summing a DAX/quick measure with column values, possible ? in DAX Commands and Tips

Summing a DAX/quick measure with column values, possible ?

Anonymous — Thu, 05 Nov 2020 10:43:08 GMT

Hello Community,

We have a table with:
Month Qty
202001 1

202002 2

202003 3

In that table, we have created a quick measure that is a DAX formula. This DAX formula sums Qty before 2020 and returns one value.
Now, suppose the result of the DAX formula is 4. I want to have a new column "Final Qty" whose value is 4 + 1 = 5 for 202001, 4 + 2 for 202002 and 4 + 3 = 7 for 202003.

Table would eventually looks like:

Month Qty DAX Final Qty

202001 1 4 5

202002 2 4 6

202003 3 4 7

( not much DAX / PBI experience I must say 🙄 but been lurking around )

Thx for shedding some lights, for the sake of my hairs 😉

Brgds,

Olivier

Re: Summing a DAX/quick measure with column values, possible ?

Fowmy — Thu, 05 Nov 2020 10:50:20 GMT

Anonymous

Not sure how your measures are built, but you can dimple added two measures:

Final Qty = [DAX] + sum(Table[Qty])

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Re: Summing a DAX/quick measure with column values, possible ?

amitchandak — Thu, 05 Nov 2020 10:51:07 GMT

Anonymous , if you have created a measure then you need to create a mesure. You can not use measure in a new column

1. You create a measure

measure =
var _1 = [Qty before 2020]
return
sum(Table[Qty]) +1

2. You create a column

new column =
var _1 = [Qty before 2020]
return
(Table[Qty]) +1

Re: Summing a DAX/quick measure with column values, possible ?

AlB — Thu, 05 Nov 2020 10:55:43 GMT

Hi Anonymous

I assume that by "table" you mean a table visual. If so,

1. Place Month in the visual, as you already had

2. Create a explicit measure for the Qty and place it in the visual:

QtyMeasure = SUM(Table1[Qty])

3. Place your DAX measure in the visual, as you already had

4. Create this measure (simply the sum of the two previous ones) and place it in the visual:

Final Qty = [QtyMeasure] + [Your DAX Measure]

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Summing a DAX/quick measure with column values, possible ?

Anonymous — Thu, 05 Nov 2020 13:34:30 GMT

Table is not a table visual, it's a table available as follow in the Data Table Tool:

The table visual in Reportin tool:

On left, a Card visualization showing Qty "before" = 3.

On right, the table visual where I would like to add "3" to all Running Total value.
So 2019 10 = 4 (1+3), 2019 11 = 4 (1+3), 2019 12 = 2 (-1+3), 2020 01 = 3 (0+3), and so on...

Re: Summing a DAX/quick measure with column values, possible ?

Anonymous — Thu, 05 Nov 2020 14:20:08 GMT

When using my DAX measure in formula, it's always empty. And when diplaying it in a Card visualization, I can see its value though.

Re: Summing a DAX/quick measure with column values, possible ?

Anonymous — Thu, 05 Nov 2020 14:21:19 GMT

When used in formula, my DAX measure is = 0/blank/empty 😞

Re: Summing a DAX/quick measure with column values, possible ?

Payeras_BI — Sun, 08 Nov 2020 14:21:11 GMT

Hi Anonymous ,

Your measure [Opened Before Reporting period] is taking into account the filter context created in your Table visualization by placing the YYYYMM in the rows.

Regards,

Re: Summing a DAX/quick measure with column values, possible ?

wdx223_Daniel — Fri, 06 Nov 2020 03:15:56 GMT

FinalTotal=CALCULATE([Opend Before Reorting Period],ALLSELECT())+[Running Total]