I need to diagonally sum columns based on the below concept in the image

[THAMAR]

Like this I have q1 to q12 columns which should work as per the below approach.

 

 

I need a dax formula to create a calculated column that can sum cells diagonally based on this approach marked by similar colours.

 

[THAMAR · Accepted solution]

@quantumudit  reply 

[Anonymous]

Hi @THAMAR ,

Is there any progress on this issue?

If you find any answer is helpful to you, please remember to accept it.

It will help others who meet the similar question in this forum.

Thank you for your understanding.

[quantumudit · Accepted solution]

Hello @THAMAR 

I hope I've understood your requirements correctly. You've provided two types of screenshots: in the first, you have columns "m1", "m2", ..., and "m6", and above them, you've mentioned a "q1 to q12" column, which is a bit confusing.

Then, you shared a screenshot with data for two quarters, colour-coded to demonstrate the desired summation method, which is intuitive and easy to understand. The summation value in the "Output Required" column also appears correct. However, the summation result in the subsequent screenshot seems incorrect. Considering the type of summation you require, I believe the value in cell H5 should be 760, not 460. Similarly, the values in cells H7, H8, and H9 should be 460, 780, and 1160, respectively.

Here is the screenshot with the correct sum:

Considering your last screenshot (with the corrected sum) as the problem statement, the following DAX formula can be used to create a calculated column to achieve the desired outcome:

Diagonal Sum = 
VAR _currentKey = DiagonalTable[Key]
VAR _currentFiscalMonthIdx = DiagonalTable[FiscalMonthIndex]

// Creating Single Cell Tables
VAR _at2Tbl = SELECTCOLUMNS(FILTER(DiagonalTable, DiagonalTable[FiscalMonthIndex]=_currentFiscalMonthIdx-1 && DiagonalTable[Key]=_currentKey), "q2", DiagonalTable[Q2_Sales])
VAR _at3Tbl = SELECTCOLUMNS(FILTER(DiagonalTable, DiagonalTable[FiscalMonthIndex]=_currentFiscalMonthIdx-2 && DiagonalTable[Key]=_currentKey), "q3", DiagonalTable[Q3_Sales])
VAR _at4Tbl = SELECTCOLUMNS(FILTER(DiagonalTable, DiagonalTable[FiscalMonthIndex]=_currentFiscalMonthIdx-3 && DiagonalTable[Key]=_currentKey), "q4", DiagonalTable[Q4_Sales])

RETURN
SWITCH(
    TRUE(),
    DiagonalTable[FiscalMonthIndex] = 1, DiagonalTable[Q1_Sales],
    DiagonalTable[FiscalMonthIndex] = 2, DiagonalTable[Q1_Sales] + _at2Tbl,
    DiagonalTable[FiscalMonthIndex] = 3, DiagonalTable[Q1_Sales] + _at2Tbl + _at3Tbl,
    DiagonalTable[FiscalMonthIndex] = 4, DiagonalTable[Q1_Sales] + _at2Tbl + _at3Tbl + _at4Tbl,
    BLANK()
)

This is the Power BI Desktop screenshot showing the comparison between the "Output Required" and "Desired Sum" columns:

This DAX pattern can be adapted for your specific use case, whether it's for two quarters or six months. Please let me know if you encounter any further challenges related to this.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨Visit My Linktree: LinkTree

[THAMAR]

Pls share the file..

[quantumudit]

Hello @THAMAR 

Attached is the solution file. If it resolves your issue, kindly mark it as the solution and leave a like. Your appreciation would be greatly valued.

Download Sample PowerBI (Link Expires on 12th Sept. 2024) 

Best Regards,
Udit

Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[THAMAR · Accepted solution]

@quantumudit  reply