DAX table to create a revenue funnel visual?

[tal_il]

Hi!

my main fact table is financial transactions which look pretty much like this:

date	type	amount
10/12/22	revenue	100$
10/12/22	expense	50$
01/01/23	revenue	100$
01/01/23	expense	30$
05/01/23	revenue	150$
05/01/23	expense	75$

I want to create a revenue funnel visual that shows the conversion between the revenue and the margins.

from what I understand, I need to get a table at this structure ( I can drop the year but I want to keep it for future visualization)

year	type	amount
2022	revenue	100$
2022	margins	(100$-50$) = 50$
2023	revenue	(100$+150$) = 250$
2023	margins	(100$+150$) - (30$+75$) =  145$

Then I will drag the type to the funnel group and the amount to the funnel value.

How can I create this table using DAX?

Thanks! Tal

[123abc]

To create a DAX table that represents the revenue funnel structure you described, you can use DAX measures and calculated tables in Power BI or any other tool that supports DAX. Here's how you can create the table step by step:

Assuming your fact table is named "Transactions," and it has columns named "Date," "Type," and "Amount," you can create the revenue funnel table using the following DAX measures and calculated table:

1. Calculate Revenue and Expense:

First, create two DAX measures to calculate the total revenue and total expenses for each year.

Total Revenue = SUMX(FILTER(Transactions, Transactions[Type] = "revenue"), Transactions[Amount])

Total Expenses = SUMX(FILTER(Transactions, Transactions[Type] = "expense"), Transactions[Amount])

1. Calculate Margins:

Next, create a DAX measure to calculate the margins for each year. You can subtract total expenses from total revenue.

Margins = [Total Revenue] - [Total Expenses]

1. Create a Calculated Table:

Now, create a calculated table that includes the desired structure with the year, type (revenue and margins), and amount.

RevenueFunnelTable =
SUMMARIZE(
Transactions,
Transactions[Year],
"Type", "revenue",
"Amount", [Total Revenue]
)
UNION
SUMMARIZE(
Transactions,
Transactions[Year],
"Type", "margins",
"Amount", [Margins]
)

This calculated table combines the revenue and margins data for each year into the structure you mentioned.

1. Visualization:

Finally, in your visualization tool (e.g., Power BI), you can use the "Year" column for the funnel group and the "Amount" column for the funnel value to create the revenue funnel visual.

Ensure that you replace "Transactions" with the actual name of your fact table and adjust column names as needed if they are different in your data model.

Once you've created this calculated table and set up the visualization, you should be able to visualize the revenue funnel as described.

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