Forum Discussion
Solved
Divide rows between dimensions based on rules
Hello there I'm working with a ledger at a firm and they divide cost that is registered on some chosen set of keys within the ledger. For example if there are 2 divisions, Marketing and Sales...
Hi Yggdrasill,
Based on my test, we can add a calculated column in your fact table using this formula.
result = var sumA = CALCULATE(SUM(fact_table[value]),FILTER(fact_table,fact_table[dimension1]="A"&& fact_table[date]=EARLIER(fact_table[date]))) var sumB = CALCULATE(SUM(fact_table[value]),FILTER(fact_table,fact_table[dimension1]="B"&& fact_table[date]=EARLIER(fact_table[date]))) var sumC = CALCULATE(SUM(fact_table[value]),FILTER(fact_table,fact_table[dimension1]="C"&& fact_table[date]=EARLIER(fact_table[date]))) return IF(fact_table[dimension1]="A", sumA+(0.25*sumC),IF(fact_table[dimension1]="B",sumB+(0.75*sumC),BLANK()))
Then we can get the result as we excepted.
For more details, please check the pbix as attached.
Regards,
Frank
Hi Yggdrasill,
Based on my test, we can add a calculated column in your fact table using this formula.
result = var sumA = CALCULATE(SUM(fact_table[value]),FILTER(fact_table,fact_table[dimension1]="A"&& fact_table[date]=EARLIER(fact_table[date]))) var sumB = CALCULATE(SUM(fact_table[value]),FILTER(fact_table,fact_table[dimension1]="B"&& fact_table[date]=EARLIER(fact_table[date]))) var sumC = CALCULATE(SUM(fact_table[value]),FILTER(fact_table,fact_table[dimension1]="C"&& fact_table[date]=EARLIER(fact_table[date]))) return IF(fact_table[dimension1]="A", sumA+(0.25*sumC),IF(fact_table[dimension1]="B",sumB+(0.75*sumC),BLANK()))
Then we can get the result as we excepted.
For more details, please check the pbix as attached.
Regards,
Frank
Hi Yggdrasill,
Does that make sense? If so, kindly mark my answer as a solution to close the case please.
Regards,
Frank
Thank you !
I simplified my original problem but I managed to use this method anyway !
