Cannot create measure for waterfall chart

[Anonymous]

Hello, 

I have the following table:

Order No	City	State	Customer Type	Effect Name	Effect Value	Income	Order Quantity	Effect Name & Customer Type
5014-1	Melbourne	VIC	Home Office	Effect A	5	2100	235	Home Office Effect A
5016-1	Melbourne	VIC	Home Office	Effect A	20	2980	228	Home Office Effect A
5018-1	Sydney	NSW	Corporate	Effect A	10	1667	190	Corporate Effect A
5019-1	Melbourne	VIC	Home Office	Effect A	18	2109	308	Home Office Effect A
5020-1	Sydney	NSW	Consumer	Effect A	1	2624	330	Consumer Effect A
5023-1	Melbourne	VIC	Home Office	Effect A	5	2194	281	Home Office Effect A
5024-1	Sydney	NSW	Corporate	Effect A	14	1732	174	Corporate Effect A
5025-1	Sydney	NSW	Consumer	Effect A	11	2039	276	Consumer Effect A
5027-1	Sydney	NSW	Small Business	Effect A	10	2685	400	Small Business Effect A
5029-1	Sydney	NSW	Corporate	Effect A	4	2318	350	Corporate Effect A
5031-1	Melbourne	VIC	Corporate	Effect A	11	1514	234	Corporate Effect A
5033-1	Sydney	NSW	Corporate	Effect A	5	2992	233	Corporate Effect A
5034-1	Sydney	NSW	Corporate	Effect A	5	1865	366	Corporate Effect A
5036-1	Melbourne	VIC	Corporate	Effect A	2	1465	247	Corporate Effect A
5037-1	Sydney	NSW	Home Office	Effect A	3	1726	400	Home Office Effect A
5038-1	Melbourne	VIC	Corporate	Effect A	6	2056	287	Corporate Effect A
5039-1	Sydney	NSW	Corporate	Effect A	2	1553	276	Corporate Effect A
5040-1	Sydney	NSW	Small Business	Effect A	5	2360	268	Small Business Effect A
5043-1	Sydney	NSW	Small Business	Effect A	14	1315	249	Small Business Effect A
5014-1	Melbourne	VIC	Home Office	Effect B	13	2100	235	Home Office Effect B
5016-1	Melbourne	VIC	Home Office	Effect B	11	2980	228	Home Office Effect B
5018-1	Sydney	NSW	Corporate	Effect B	10	1667	190	Corporate Effect B
5019-1	Melbourne	VIC	Home Office	Effect B	14	2109	308	Home Office Effect B
5020-1	Sydney	NSW	Consumer	Effect B	12	2624	330	Consumer Effect B
5023-1	Melbourne	VIC	Home Office	Effect B	14	2194	281	Home Office Effect B
5024-1	Sydney	NSW	Corporate	Effect B	3	1732	174	Corporate Effect B
5025-1	Sydney	NSW	Consumer	Effect B	1	2039	276	Consumer Effect B
5027-1	Sydney	NSW	Small Business	Effect B	6	2685	400	Small Business Effect B
5029-1	Sydney	NSW	Corporate	Effect B	19	2318	350	Corporate Effect B
5031-1	Melbourne	VIC	Corporate	Effect B	3	1514	234	Corporate Effect B
5033-1	Sydney	NSW	Corporate	Effect B	9	2992	233	Corporate Effect B
5034-1	Sydney	NSW	Corporate	Effect B	18	1865	366	Corporate Effect B
5036-1	Melbourne	VIC	Corporate	Effect B	7	1465	247	Corporate Effect B
5037-1	Sydney	NSW	Home Office	Effect B	18	1726	400	Home Office Effect B
5038-1	Melbourne	VIC	Corporate	Effect B	14	2056	287	Corporate Effect B
5039-1	Sydney	NSW	Corporate	Effect B	2	1553	276	Corporate Effect B
5040-1	Sydney	NSW	Small Business	Effect B	6	2360	268	Small Business Effect B
5043-1	Sydney	NSW	Small Business	Effect B	13	1315	249	Small Business Effect B

I need to create a waterfall chart based on this table, which also has different filters (in this example it is only one filter):

• And it changes to this if it is filtered:

The problem is:

• I need to calculate the price ( dividing Income with Order Quantity ) based on the filters that are selected.
  • I have 3 measures for this, one is the sum of Income, second is the sum of Order Quantity and third is the division between Income measure and Quantity Measure.
• This price needs to be in the starting point of the waterfall chart.

Using the measure of Price is very simple, but I can't seem to grasp how to include that measure (that is calculated again based on the filters that are selected) in the starting point of the waterfall chart.

The desired outcome would be something like this:

• With the difference that price is the starting point and with a different colour.

Any suggestion would be deeply appreciated,

Thanks.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

You can create the following measure and then use the Ultimate Waterfall visual.

Price = 
SUMX ( ALLSELECTED ( 'Table' ), 'Table'[Income] )
    / SUMX ( ALLSELECTED ( 'Table' ), 'Table'[Order Quantity] )

Or use the following measures and Simple Waterfall visual.

Price_Measure = 
DIVIDE (
    [Price],
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Effect Name & Customer Type] ),
        ALLSELECTED ( 'Table' )
    )
)

Effect Value_Measure = SUM ( 'Table'[Effect Value] ) + [Price_Measure]

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.