Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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, that split IT cost between them with set of rules. For example, Marketing pays 75% of everything that is registered on the IT cost key, and Sales department takes 25%
Everything is in one fact table. I queried everything out of that ledger except for the cost keys. I took the cost keys aside and queried them in another table. Then I created a column for each department which basically took their share of the total amount. Then I pivoted those columns and voila ! I have a table with split cost between departments !
Final step was to append that cost table to the facttable. This seems to be working but I'm not sure if this is the best way to do this so my question here is...... Is there any better way to do this ? Performance wise ?
For those who don't like to read too much, see the picture below 🙂
Solved! Go to Solution.
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
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.
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
Thank you !
I simplified my original problem but I managed to use this method anyway !
Hi @Yggdrasill,
Does that make sense? If so, kindly mark my answer as a solution to close the case please.
Regards,
Frank
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |