Forum Discussion

Swalker's avatar
Swalker
Frequent Visitor
3 years ago

UNPIVOT in Power Pivot for Excel

Hi,   I am struggling with developing an unpivot algorithm in Power Pivot for Excel.   I am loading Transactional Date/LedgerKey/Amount data via ODBC in PowerPivot for Excel DataModel in table TL...
Data Modeling
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Swalker , First you need to unpivot , then join with date table and then create cumulative like formula below or use windows function

 

Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(allselected(date),date[date] <=max(date[Date])))

 

https://radacad.com/pivot-and-unpivot-with-power-bi

Winow , Offset - https://youtu.be/cN8AO3_vmlY?t=31980

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Swalker's avatar
Swalker
Frequent Visitor
3 years ago

Dear Amit,

Thanks for your reply.

The issue is that the table is now in the DataModel Power Pivot for Excel as a tab [RTTable] but i cannot figure out how to load this in Power Query inorder to unpivot.  

any ideas ?