topic Re: DAX formula in DAX Commands and Tips

DAX formula

Anonymous — Wed, 05 Jan 2022 14:31:54 GMT

Hi all,

Dates are the columns and it goes until 4/1/22.

How can I add it all and get the sum at the end.

Also, the data keeps changing and the total should change accordingly.

How can I do that?

Any help would be appreciated!

Thank you!

Megha

Re: DAX formula

filipeoliveira — Wed, 05 Jan 2022 15:19:41 GMT

If you want to sum the total value by row, you can create a calculated column in your table with something like this:

NEW_COLUMN = CALCULATE(SUM(YOUR_TABLE[VALUES_COLUMN]),ALL(YOUR_TABLE))

And then you can add that column in your table visual, so it will show the total by row.

Re: DAX formula

smpa01 — Wed, 05 Jan 2022 15:51:07 GMT

Anonymous first tsep unpivot the data in PQ

Re: DAX formula

Anonymous — Wed, 05 Jan 2022 16:24:19 GMT

Hi smpa01 ,

Thank you for your response!

But I want the data to be viewed that way. And hence I did pivot.

Is there any other way?

Thank you!

Megha

Re: DAX formula

AlexisOlson — Wed, 05 Jan 2022 21:37:24 GMT

Just because you want the data to be viewed that way doesn't mean it should be stored that way in the model. If you unpivot it, you can still easily put it back in this format with a matrix visual.

Even if you don't ultimately want to have it unpivoted, you can unpivot it to do calculations much more easily and then re-pivot it at the end to get it back in that format.

Re: DAX formula

Anonymous — Thu, 06 Jan 2022 09:45:56 GMT

If I do not pivot, this is how it looks like.

Each part number have the same date.

And hence, if I pivot, the dates are in the column, and I can know the quantity for each part number in an easier way.

Is there any other way to do that?

And I want all of this in the tablular form, and hence not using the visualisations.

Re: DAX formula

AlexisOlson — Thu, 06 Jan 2022 14:59:40 GMT

If you don't plan on using measures and visuals, then I guess the format doesn't really matter so much but then Excel might be a better tool.

Re: DAX formula

Anonymous — Thu, 06 Jan 2022 15:12:24 GMT

I have it in excel, but most of it done manually and hence planned on using powerBI because most of it can be automated.

Also, I am not familiar with VBA and hence using PowerBI.

Re: DAX formula

Icey — Mon, 10 Jan 2022 05:58:17 GMT

Hi Anonymous ,

This table is a two-dimensional table. It is common in Excel. Two-dimensional tables have these advantages:

1. more in line with reading habits;

2. more condensed information;

3. suitable for displaying results.

However, when performing data analysis as source data, a one-dimensional table is more suitable.

So it is suggested to keep you data like below and show your data use Matrix visual as what AlexisOlson mentioned. It can show data as a 2D table.

Without considering other issues, consider this situation you mentioned:

Dates are the columns and it goes until 4/1/22.

How can I add it all and get the sum at the end.

Also, the data keeps changing and the total should change accordingly.

If you have dates as column names, when a new date generates, a new column generates in Power BI accordingly. But Power BI won't recognize the new column and an error like "can't find column "xxx"" will occur. This way your data can't be refreshed dynamically.

Hope I explained it clearly.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX formula

Anonymous — Mon, 10 Jan 2022 10:46:09 GMT

Icey Thank you so much for clear explanation!