Waterfall chart based on two columns and 1 measure

[Adajames123]

Hi, 

I am trying to create a waterfall based on Dept and Structure columns along with the associated cost .

Each project has Revenue and Total costs per structure.

I am looking to create a chart with this structure on the x-axis :
1 = Revenue at Structure 1 - Total costs at Structure 1
2 = Revenue at Structure 2 - Total costs at Structure 2
HR = HR at Structure 2 - HR at Structure 3
PM = PM at Structure 2 - PM at Structure 3
Logistics = Logistics at Structure 2 - Logistics at Structure 3
3 = Revenue at Structure 3 - Total costs at Structure 3

I specifically want HR, PM, and Logistics to be displayed only between 2 and 3 on the x-axis, as this is the main focus of the analysis.

Project	Dept	Structure	Cost
Project A	HR	2	4,500.00
Project A	HR	3	5,500.00
Project A	PM	1	7,000.00
Project A	PM	2	6,000.00
Project A	PM	3	7,500.00
Project A	Logistics	1	11,500.00
Project A	Logistics	2	13,000.00
Project A	Logistics	3	12,000.00
Project A	Revenue	1	5,500.00
Project A	Revenue	2	7,000.00
Project A	Revenue	3	8,000.00
Project A	Total costs	1	23,500.00
Project A	Total costs	2	24,500.00
Project A	Total costs	3	26,000.00
Project B	HR	1	4,500.00
Project B	HR	2	4,000.00
Project B	HR	3	5,000.00
Project B	PM	1	6,000.00
Project B	PM	2	5,000.00
Project B	PM	3	6,500.00
Project B	Logistics	1	15,000.00
Project B	Logistics	2	14,000.00
Project B	Logistics	3	16,000.00
Project B	Revenue	1	6,000.00
Project B	Revenue	2	7,000.00
Project B	Revenue	3	8,000.00
Project B	Total costs	1	26,500.00
Project B	Total costs	2	26,500.00
Project B	Total costs	3	27,000.00

Please find attached the pbix file
Waterfall.pbix 

Looking forward to your suggestions and feedback!

Thank you.

[Anonymous · Accepted solution]

Hi @Adajames123 ,

I can’t open the link file.

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the measures to calculate cost.

Measure 1 = CALCULATE(
    SUM('Table test'[Cost]),
    FILTER(
        ALLEXCEPT('Table test','Table test'[Project]), 'Table test'[Dept] = "Revenue" && 'Table test'[Structure] = 1
    )
) -
CALCULATE(
    SUM('Table test'[Cost]),
    FILTER(
        ALLEXCEPT('Table test', 'Table test'[Project]), 'Table test'[Dept] = "Total costs" && 'Table test'[Structure] = 1
    )
)

Measure 2 = CALCULATE(
    SUM('Table test'[Cost]),
    FILTER(
        ALLEXCEPT('Table test', 'Table test'[Project]), 'Table test'[Dept] = "Revenue" && 'Table test'[Structure] = 2
    )
) -
CALCULATE(
    SUM('Table test'[Cost]),
    FILTER(
        ALLEXCEPT('Table test', 'Table test'[Project]),
        'Table test'[Dept]= "Total costs" && 'Table test'[Structure] = 2
    )
)

Measure HR = CALCULATE(
    SUM('Table test'[Cost]),
    FILTER(
        ALLEXCEPT('Table test', 'Table test'[Project]), 'Table test'[Dept] = "HR" && 'Table test'[Structure] = 2
    )
) -
CALCULATE(
    SUM('Table test'[Cost]),
    FILTER(
        ALLEXCEPT('Table test', 'Table test'[Project]), 'Table test'[Dept]= "HR" && 'Table test'[Structure] = 3
    )
)

Measure pm = CALCULATE(
    SUM('Table test'[Cost]),
    FILTER(
        ALLEXCEPT('Table test', 'Table test'[Project]), 'Table test'[Dept] = "PM" && 'Table test'[Structure] = 2
    )
) -
CALCULATE(
    SUM('Table test'[Cost]),
    FILTER(
        ALLEXCEPT('Table test', 'Table test'[Project]), 'Table test'[Dept]= "PM" && 'Table test'[Structure] = 3
    )
)

Measure Logis = CALCULATE(
    SUM('Table test'[Cost]),
    FILTER(
        ALLEXCEPT('Table test', 'Table test'[Project]), 'Table test'[Dept] = "Logistics" && 'Table test'[Structure] = 2
    )
) -
CALCULATE(
    SUM('Table test'[Cost]),
    FILTER(
        ALLEXCEPT('Table test', 'Table test'[Project]), 'Table test'[Dept]= "Logistics" && 'Table test'[Structure] = 3
    )
)

3.Create the new table.

4.Create the measure to show values.

Measure values = 
SWITCH(
    SELECTEDVALUE('X-axis name'[column name]),
    "1", [Measure 1],
    "2", [Measure 2],
    "HR", [Measure HR],
    "PM", [Measure pm],
    "Logistics", [Measure Logis],
    "3", [Measure 3]
)

5.Drag the two columns and measure into the waterfall chart.

6.The result is shown below.

You can also view the following links to learn about waterfall chart.

Solved: Waterfall chart with multiple measures - Microsoft Fabric Community

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Adajames123 ,

try below measure

HR_Difference = CALCULATE(
    SUM('YourTableName'[Cost]),
    FILTER(
        ALL('YourTableName'),
        'YourTableName'[Dept] = "HR" &&
        'YourTableName'[Structure] = 2
    )
) -
CALCULATE(
    SUM('YourTableName'[Cost]),
    FILTER(
        ALL('YourTableName'),
        'YourTableName'[Dept] = "HR" &&
        'YourTableName'[Structure] = 3
    )
)