Showing results for 
Search instead for 
Did you mean: 
Not applicable

dax formula - column to row without merging



I have 2 excel tables as the source in Power BI

1 table - called pipeline - the volume that is set with valid to and from - will be divided in corresponding weeks.

SalesValid FromValid ToVolumeValid From WeekValid To WeekTotal Week InvolveWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11Week 12Week 13Week 14Week 15Week 16Week 17Week 18
C1-Mar-2031-Mar-203009146        505050505050    
D1-Feb-2031-Mar-206051410    6666666666    


1 table - called target - the weekly target volume each week

Week 150A
Week 250A
Week 350A
Week 450A
Week 550A
Week 650A
Week 750A
Week 850A
Week 950A
Week 1050A
Week 1150A
Week 1250A
Week 1350A
Week 1450A
Week 1550A
Week 1650A
Week 1750A
Week 1850A
Week 1950A
Week 160B
Week 260B
Week 360B
Week 460B
Week 560B
Week 660B
Week 760B
Week 860B
Week 960B
Week 1060B
Week 1160B
Week 1260B
Week 1360B
Week 1460B
Week 1560B
Week 1660B
Week 1760B
Week 1860B
Week 1960B
Week 125C
Week 225C
Week 325C
Week 425C
Week 525C
Week 625C
Week 725C
Week 825C
Week 925C
Week 1025C
Week 1125C
Week 1225C
Week 1325C
Week 1425C
Week 1525C
Week 1625C
Week 1725C
Week 1825C
Week 1925C
Week 160D
Week 260D
Week 360D
Week 460D
Week 560D
Week 660D
Week 760D
Week 860D
Week 960D
Week 1060D
Week 1160D
Week 1260D
Week 1360D
Week 1460D
Week 1560D
Week 1660D
Week 1760D
Week 1860D
Week 1960D


in PowerBI, I would like to have this kind of result. Target from the sum of value each week from table target, and pipeline the sum based on week from table pipeline

Week 11953.571429
Week 21953.571429
Week 31953.571429
Week 41953.571429
Week 51953.571429
Week 61953.571429
Week 719526.2381
Week 819526.2381
Week 919576.2381
Week 1019576.2381
Week 1119576.2381
Week 1219576.2381
Week 1319576.2381
Week 1419576.2381
Week 1519516.66667
Week 1619516.66667
Week 1719516.66667
Week 1819516.66667


Currently, to achieve the table in PowerBI - I am using merge queries & aggreagate by creating unique identifier to link both table, at first it works perfectly. Now with the data are coming in, the loading process is taking a bit too long.


So I am to streamline the process through Dax formula where I can use to create this table without doing the function of merge queries & aggregate.


Need the help here!


Thanks in advance.




Super User
Super User

I would strongly considering unpivoting your Week columns in your pipeline table. You could then use SUMMARIZE on your target table by Week, SUM your Volume column and then probably do a LOOKUPVALUE to your pipeline column or a SUMX with a FILTER on RELATEDTABLE.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Not applicable

Hi Greg,

The data is original until valid to.
When I set it as source in excel, i create the additional column starts from valid from week, valid to week, total week involve and set formula for each week x, week y, ...
So that when new data are coming in, it can determine to which week the volume should be distributed in order to create pipeline.

I guess this is the one that create the problem.
Is this possible if we can set dax to identify the pipeline for each week?


Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors