Forum Discussion

JasMack's avatar
JasMack
Frequent Visitor
3 years ago
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 ...
Need Help
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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.