Forum Discussion
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 | 75 | 80 |
| B | Sarah | 1 | 300 | 400 |
| B | Sarah | 2 | 150 | 500 |
Can you please help me with the achieving the following results on Power BI desktop using the table above?
| Particular | Prior Month (Period <2) | Year to date |
| Cost | 450 | 825 |
| Revenue | 690 | 1370 |
Thanks.
Jason
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.
2 Replies
- Greg_Deckler
Community Champion
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.
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
