DAX formula

[Anonymous]

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

[Icey · Accepted solution]

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.

[Anonymous]

@Icey Thank you so much for clear explanation!

[smpa01]

@Anonymous  first tsep unpivot the data in PQ

[Anonymous]

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

[AlexisOlson]

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.

[Anonymous]

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.

[AlexisOlson]

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.

[Anonymous]

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. 

[filipeoliveira]

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.