Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone
I have two fact tables connected to a dimension table. Fact tables have a many-to-one relationship with Dimension.
I have the following fields displayed in a matrix in the Power BI visual.
I have a category that comes from the Dim table
Date, Weeks, A (measure), Cumulative Sum of A are fields and measures created using Fact Table 1.
Fact table 2 has summed values to make Measure B (Excel cell G above)
The cumulative sum of A - B gives the difference in measure [Cumulative sum of A] - [B]. (Cell H)
All these measures are returning correct values and well so far.
However, I face a challenge in calculating the calculation field (I) since its formula is MAX (Cumulative Sum of A) - [B] - Previous Result).
For example, for row 2, the result would be 0.
For row 3 the score would be 0-0.
For row 4 the result would be MAX(1147.83 -(0+0),0)= 1147.83
For row 5 the result would be MAX(1066.57 -(1147.83+0+0),0)= 0
So, basically the sum of the previous rows is subtracted from the current row. I tried to create in the same way as I have with the cumulative sum of measure A, but since B comes from fact table 2 which has no index, the same concept doesn't apply there.
Please let me know if anything is unclear.
@Syndicate_Admin , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Window and Index function can help, Or visual calcutions can help
Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U
Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f
Master Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
Next-Level Power BI Visual Calculations with Offset, Index, Rank, RowNumber| February 2024 Update:
https://www.youtube.com/watch?v=JwMF0endTPY&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L