Forum Discussion
DAX formula
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
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.
9 Replies
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.
Icey Thank you so much for clear explanation!
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.Anonymous first tsep unpivot the data in PQ
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
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.
