How to Group Calculated columns to display them in a Waterfall chart

[Kumar_0606]

Hello Experts,

I have logic like this in Tableau

CASE Index()

WHEN 1 THEN 'Calculated column1'

WHEN 2 THEN ' Calculated column2'

WHEN 3 THEN 'Calculated column3'

WHEN 4 THEN 'Calculated column4'

END

Similarly for Measures

 CASE INDEX()

WHEN 1 THEN TOTAL(Calculated column1)

WHEN 2 THEN TOTAL(Calculated column2)

WHEN 3 THEN TOTAL( Calculated column3)

WHEN 4 THEN TOTAL(Calculated cooumn4)

END

I have created calculated columns here and wondering how to apply the same logic in Power BI to use them in Waterfall chart.

Also what is the relevant function to TOTAL.

Thanks in advance.

[mark_endicott · Accepted solution]

@Kumar_0606 - You could use a Field Parameter for the columns: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters 

Or you could do this with DAX with a disconnected table and SWITCH():

First create a disconnected table, use below DAX 

WaterfallItems = DATATABLE(
"Item", STRING,
{
{"Column 1 Name"},
{"Column 2 Name"},
{"Column 3 Name"},
{"Column 4 Name"}
}
)

Now these columns can be dynamically displayed using the DAX Measure below

WaterfallValue =
SWITCH(
SELECTEDVALUE(WaterfallItems[Item]),
"Column 1 Name", SUM(Table[Column 1]),
"Column 2 Name", SUM(Table[Column 2]),
"Column 3 Name", SUM(Table[Column 3]),
"Column 4 Name", SUM(Table[Column 4]),
BLANK()
)

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[v-mdharahman]

Hi @Kumar_0606,

Thanks for reaching out to the Microsoft fabric community forum.

I understand you're trying to replicate Tableau's CASE INDEX() logic in Power BI for use in a Waterfall chart, including custom logic for a cumulative running total with conditions.

You've made good progress by creating a disconnected parameter table and using SWITCH() for dynamic measure selection. The next step is calculating a cumulative value like Tableau's RUNNING_SUM() needs to be handled a bit differently in Power BI, since RUNNINGSUM() is only available in Visual Calculations, and calculated columns won’t help for this purpose. 
@mark_endicott correctly guided you to use a disconnected table and a SWITCH measure to simulate dynamic selection. However, the challenge arises when trying to implement a cumulative logic like RUNNINGSUM()which is not available as a calculated column in Power BI.
You can try this workaround:

* Create a disconnected table and use this dax:
WaterfallItems = DATATABLE(
"Item", STRING,
{
{"Column 1"},
{"Column 2"},
{"Column 3"},
{"Column 4"}
}
)
Make sure the column name "Item" matches exactly when referenced later.
* Create a dynamic measure for selected column values.
WaterfallValue =
SWITCH(
SELECTEDVALUE(WaterfallItems[Item]),
"Column 1", SUM('YourTable'[Column1]),
"Column 2", SUM('YourTable'[Column2]),
"Column 3", SUM('YourTable'[Column3]),
"Column 4", SUM('YourTable'[Column4]),
BLANK()
)
Make sure to replace 'YourTable' with your actual table name.
* Now create an index column in the parameter table. Power BI needs a way to order your items to compute running totals. You can manually add an index like this:

WaterfallItems = DATATABLE(
"Item", STRING,
"Order", INTEGER,
{
{"Column 1", 1},
{"Column 2", 2},
{"Column 3", 3},
{"Column 4", 4}
}
)

* Now that we have an order, we can use CALCULATE with a filter to simulate running total. This measure sums up all WaterfallValues up to the current row (like Tableau’s RUNNING_SUM()).

CumulativeWaterfallValue =
VAR CurrentOrder = SELECTEDVALUE(WaterfallItems[Order])
RETURN
CALCULATE(
[WaterfallValue],
FILTER(
ALL(WaterfallItems),
WaterfallItems[Order] <= CurrentOrder
)
)

* Now make your Waterfall Chart. Use WaterfallItems[Item] on Category/Axis and [WaterfallValue] or [CumulativeWaterfallValue] as Values, depending on what you want. If you want to show the contribution of each column, use WaterfallValue and if you want to show the cumulative build-up, use CumulativeWaterfallValue.

I would also take a moment to thank @mark_endicott, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

[Kumar_0606]

Hello Hammad,

Thanks for the response.

I created the Cumulative value measure as you suggested.

When I drag only Cumulative value in Y-axis along with WaterfallItems[Item] i am getting only one column i.e, Column1.

I don't understand the behaviour.

[v-mdharahman]

Hi @Kumar_0606,

When you position WaterfallItems[Item] on the X-axis and CumulativeWaterfall on the Y-axis, Power BI calculates the measure row by row for each item. If you're only getting one column (for example, for "Column 1 Name"), it probably is because the measure isn't returning values for other items because of the way the cumulative logic is coded. The disconnected table isn't sorted or indexed correctly, so the cumulative logic doesn't know how to roll up across items. The visual context is not causing the measure to work as expected, particularly if the measure is based on SELECTEDVALUE() or LOOKUPVALUE() in the absence of proper relationship or index.

* Include an index column in your disconnected table, rather than depending on position in a list, define an index explicitly:

WaterfallItems = DATATABLE(
"Item", STRING,
"Index", INTEGER,
{
"{\"Column 1 Name\"", 1},
"{\"Column 2 Name\"", 2},
"{\"Column 3 Name\"", 3},
"{\"Column 4 Name\"", 4}
}

* Now modify your measure to utilize the index. This iteration sums values by the index:
CumulativeWaterfall =
VAR CurrentIndex = MAX(WaterfallItems[Index])
RETURN
CALCULATE(
SUMX(
FILTER(
WaterfallItems,
WaterfallItems[Index] <= CurrentIndex
),
SWITCH(
WaterfallItems[Item],
"Column 1 Name", SUM(Table[Column1]),
"Column 2 Name", SUM(Table[Column2]),
"Column 3 Name", SUM(Table[Column3]),
"Column 4 Name", SUM(Table[Column4]),
BLANK()
)
)
)
This action cycles through all items through the current index and adds their values. It is used correctly in visualizations such as Waterfall charts.
Use WaterfallItems[Item] on the X-axis and CumulativeWaterfall as the Y-axis value. Make sure also that the disconnected table is ordered by Index in ascending order.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[Kumar_0606]

Hello Hammad,

I started creating the table and values getting Unexpected expression "{\ in the start itself Column 1.

[v-mdharahman · Accepted solution]

Hi @Kumar_0606,

The issue arises when trying to use "{" and "\" improperly ikely while entering the DATATABLE formula in the Power BI formula bar.

Please check the correct way:

WaterfallItems =
DATATABLE(
"Item", STRING,
"Order", INTEGER,
{
{"Column 1", 1},
{"Column 2", 2},
{"Column 3", 3},
{"Column 4", 4}
}
)

[v-mdharahman]

Hi @Kumar_0606,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well.

And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,
Hammad.

[Kumar_0606]

Did anyone come across or have knowledge in this type of requirement, please do provide me the solution.

[mark_endicott · Accepted solution]

@Kumar_0606 - You could use a Field Parameter for the columns: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters 

Or you could do this with DAX with a disconnected table and SWITCH():

First create a disconnected table, use below DAX 

WaterfallItems = DATATABLE(
"Item", STRING,
{
{"Column 1 Name"},
{"Column 2 Name"},
{"Column 3 Name"},
{"Column 4 Name"}
}
)

Now these columns can be dynamically displayed using the DAX Measure below

WaterfallValue =
SWITCH(
SELECTEDVALUE(WaterfallItems[Item]),
"Column 1 Name", SUM(Table[Column 1]),
"Column 2 Name", SUM(Table[Column 2]),
"Column 3 Name", SUM(Table[Column 3]),
"Column 4 Name", SUM(Table[Column 4]),
BLANK()
)

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[Kumar_0606]

Hello Mark,

Thanks for the response.

I created a disconnected table and when i tried to create Waterfallvalue calculation it says "Waterfallitems[item] is not recognised.

Secondly after this i need to create a calculated column called Cumulative Value to be used in the chart like

IF WaterfallItems = "[Column1]" THEN [WaterfallValue] ELSEIF

WaterfallItems = "[Column4]" THEN [WaterfallValue]

ELSE

RUNNINGSUM([WaterfallValue])

END

Please assist.

[mark_endicott]

If you have used my code to create the table then your Waterfallitems[item] has a lower case i for Item, whereas mine is upper case. If it's not finding the column, you should check this.

You won't be able to achieve the results of a cumulative sum as a calculated column with RUNNINGSUM() this is specifically a function used in Visual calculations. Have you tried this?

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[Kumar_0606]

I'm able to find the column now.

Is there any other way where i can implement the RUNNINGSUM() for the above condition.