Forum Discussion

Yggdrasill's avatar
Yggdrasill
Responsive Resident
8 years ago
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...
Need Help
Show and Tell
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    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.

     

    https://www.dropbox.com/s/x8mlm4jm0j9z79a/Divide%20rows%20between%20dimensions%20based%20on%20rules.pbix?dl=0

     

    Regards,

    Frank

     

sidraa786's avatar
sidraa786
Regular Visitor
4 years ago

Hello,

I have a table in matrix BI. I am trying to divide AC sales to rest of the rows. My desired result should be column CY%. I have period, qtd (quarter to date), ytd (year to date). I do have different states. So, everytime i divide ac sales (1st row), it divides the rest of the rows based on given conditions below.