Cannot create measure for waterfall chart

Question

Hello,&nbsp;I have the following table:&nbsp;Order NoCityStateCustomer TypeEffect NameEffect ValueIncomeOrder QuantityEffect Name &amp; Customer Type5014-1MelbourneVICHome OfficeEffect A52100235Home Office Effect A5016-1MelbourneVICHome OfficeEffect A202980228Home Office Effect A5018-1SydneyNSWCorporateEffect A101667190Corporate Effect A5019-1MelbourneVICHome OfficeEffect A182109308Home Office Effect A5020-1SydneyNSWConsumerEffect A12624330Consumer Effect A5023-1MelbourneVICHome OfficeEffect A52194281Home Office Effect A5024-1SydneyNSWCorporateEffect A141732174Corporate Effect A5025-1SydneyNSWConsumerEffect A112039276Consumer Effect A5027-1SydneyNSWSmall BusinessEffect A102685400Small Business Effect A5029-1SydneyNSWCorporateEffect A42318350Corporate Effect A5031-1MelbourneVICCorporateEffect A111514234Corporate Effect A5033-1SydneyNSWCorporateEffect A52992233Corporate Effect A5034-1SydneyNSWCorporateEffect A51865366Corporate Effect A5036-1MelbourneVICCorporateEffect A21465247Corporate Effect A5037-1SydneyNSWHome OfficeEffect A31726400Home Office Effect A5038-1MelbourneVICCorporateEffect A62056287Corporate Effect A5039-1SydneyNSWCorporateEffect A21553276Corporate Effect A5040-1SydneyNSWSmall BusinessEffect A52360268Small Business Effect A5043-1SydneyNSWSmall BusinessEffect A141315249Small Business Effect A5014-1MelbourneVICHome OfficeEffect B132100235Home Office Effect B5016-1MelbourneVICHome OfficeEffect B112980228Home Office Effect B5018-1SydneyNSWCorporateEffect B101667190Corporate Effect B5019-1MelbourneVICHome OfficeEffect B142109308Home Office Effect B5020-1SydneyNSWConsumerEffect B122624330Consumer Effect B5023-1MelbourneVICHome OfficeEffect B142194281Home Office Effect B5024-1SydneyNSWCorporateEffect B31732174Corporate Effect B5025-1SydneyNSWConsumerEffect B12039276Consumer Effect B5027-1SydneyNSWSmall BusinessEffect B62685400Small Business Effect B5029-1SydneyNSWCorporateEffect B192318350Corporate Effect B5031-1MelbourneVICCorporateEffect B31514234Corporate Effect B5033-1SydneyNSWCorporateEffect B92992233Corporate Effect B5034-1SydneyNSWCorporateEffect B181865366Corporate Effect B5036-1MelbourneVICCorporateEffect B71465247Corporate Effect B5037-1SydneyNSWHome OfficeEffect B181726400Home Office Effect B5038-1MelbourneVICCorporateEffect B142056287Corporate Effect B5039-1SydneyNSWCorporateEffect B21553276Corporate Effect B5040-1SydneyNSWSmall BusinessEffect B62360268Small Business Effect B5043-1SydneyNSWSmall BusinessEffect B131315249Small Business Effect B&nbsp;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:&nbsp;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.&nbsp;The desired outcome would be something like this:With the difference that price is the starting point and with a different colour.&nbsp;Any suggestion would be deeply appreciated,Thanks.&nbsp;

v-kkf-msft · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
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] )

&nbsp;
Or use the following measures and Simple Waterfall visual.
&nbsp;
Price_Measure = 
DIVIDE (
    [Price],
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Effect Name &amp; Customer Type] ),
        ALLSELECTED ( 'Table' )
    )
)Effect Value_Measure = SUM ( 'Table'[Effect Value] ) + [Price_Measure]

&nbsp;
&nbsp;
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,WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;
&nbsp;
&nbsp;

Forum Discussion

Cannot create measure for waterfall chart

1 Reply

Related content

Waterfall chart with multiple measures

Waterfall Chart

Waterfall Chart Help

Waterfall Chart using Measures

Waterfall with multiple values

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