Forum Discussion
Solved
Get a summary table from transaction table
Hi everyone, I am new here. I have a table which looks like this. Team Person Period Cost Revenue A Tom 1 100 200 A Tom 2 150 100 A Jack 1 50 90 A Jack 2 ...
JasMack Create a disconnected table like this:
Particulars = { "Cost", "Revenue" }Create these measures:
Revenue YTD = SUM('Table'[Revenue]) Cost YTD = SUM('Table'[Cost]) Revenue Prior = SUMX(FILTER('Table',[Period] = 1), [Revenue]) Cost Prior = SUMX(FILTER('Table', [Period] = 1), [Cost]) Prior Month (Period < 2) = IF(MAX('Particulars'[Column1]) = "Cost", [Cost Prior], [Revenue Prior]) Year to Date = IF(MAX('Particulars'[Column1]) = "Cost", [Cost YTD], [Revenue YTD])Put Column1 from Particulars in your table visual along with the last 2 measures.
Greg_Deckler
Community Champion
Community ChampionJasMack Create a disconnected table like this:
Particulars = { "Cost", "Revenue" }Create these measures:
Revenue YTD = SUM('Table'[Revenue])
Cost YTD = SUM('Table'[Cost])
Revenue Prior = SUMX(FILTER('Table',[Period] = 1), [Revenue])
Cost Prior = SUMX(FILTER('Table', [Period] = 1), [Cost])
Prior Month (Period < 2) = IF(MAX('Particulars'[Column1]) = "Cost", [Cost Prior], [Revenue Prior])
Year to Date = IF(MAX('Particulars'[Column1]) = "Cost", [Cost YTD], [Revenue YTD])Put Column1 from Particulars in your table visual along with the last 2 measures.
Many thanks Greg_Deckler .
But I have 2 more questions.
1. My result table automatically gets sorted alphabetically. Is there a way to stop that. For example: I would prefer to have "Revenue" instead of "Cost" as first row.
| Particular | Prior Month (Period <2) | Year to date |
| Cost | 450 | 825 |
| Revenue | 690 | 1370 |
2. Suppose I need to have put the values in row revenue in percentage format. Is it possible?
Thank you again.
Jason