How to fill each row of a measure based on a single row value? (DAX Measure for Waterfall Chart)

dhkhanna — Tue, 01 Mar 2022 19:48:52 GMT

I have a modified data table as seen below for a Waterfall visual. This table is set-up to have an Attributes column that contains labels, and Values column that contains corresponding numeric values. Italicized columns for ValuePaid, TotalPaidTime and ValueTotal are additional measures that I have created.

LineSK	ValuePaid	TotalPaidTime	Attribute	Value	ValueTotal
1			Non Operating Time	1220	13.93%
1		7540	Total Paid Time	7540	86.07%
1	14		Maintenance	14
1	138		Changeover	138
1	160		Clean / Sanitation	160
1	947.18		Other	947.18
1	6280.82		USLE	6280.82

The table is meant to power two separate Waterfall charts. As such, I have created 2 measures as follows:

ValueTotal = CALCULATE(AVERAGEX('Waterfall Table','Waterfall Table'[Value] / (365*24)), FILTER('Waterfall Table', 'Waterfall Table'[Attribute] = "Total Paid Time" || 'Waterfall Table'[Attribute] = "Non Operating Time"))

ValuePaid = CALCULATE(AVERAGEX('Waterfall Table', 'Waterfall Table'[Value]), FILTER('Waterfall Table', 'Waterfall Table'[Attribute] <> "Total Paid Time" && 'Waterfall Table'[Attribute] <> "Non Operating Time"))

As seen above, ValueTotal is calculated as [Value] / (365*24). The (365*24) is a static total that [Value] is divided by to convert the measure to be "% of grand total".

The same is required for ValuePaid, but with a dynamic denominator of [Value] for [Attribute] = [Total Paid Time] for each LineSK.

I tried creating a measure called

TotalPaidTime = CALCULATE(AVERAGEX(FILTER('Waterfall Table','Waterfall Table'[Attribute] = "Total Paid Time"), 'Waterfall Table'[Value]) ). My thinking was that I could divide the [Value] in ValuePaid with this measure.

As seen from the sample data, this measure only populates the row where attribute itself is Total Paid Time, and not across all rows. I am therefore unable to divide the [Value] for measure ValuePaid by TotalPaidTime to get "% of grand total%", as doing so results in division by blank cells.

How can I set-up a measure where each row of ValuePaid is divided by the TotalPaidTime i.e., a table that looks like this -

LineSK	ValuePaid	TotalPaidTime	Attribute	Value	ValueTotal
527		7540	Non Operating Time	1220	13.93%
527		7540	Total Paid Time	7540	86.07%
527	0.19%	7540	Maintenance	14
527	1.83%	7540	Changeover	138
527	2.12%	7540	Clean / Sanitation	160
527	12.56%	7540	Other	947.18
527	83.30%	7540	USLE	6280.82

Re: How to fill each row of a measure based on a single row value? (DAX Measure for Waterfall Chart)

Anonymous — Fri, 04 Mar 2022 01:23:06 GMT

HI dhkhanna,

You can try to use the following measure formula to get the percent of 'current value' divided 'total value' and group by 'LineSK'.

ValuePaid = VAR currAttribute = SELECTEDVALUE ( 'Waterfall Table'[Attribute] ) VAR total = CALCULATE ( AVERAGE ( 'Waterfall Table'[Value] ), FILTER ( ALLSELECTED ( 'Waterfall Table' ), 'Waterfall Table'[Attribute] = "Total Paid Time" ), VALUES ( 'Waterfall Table'[LineSK] ) ) RETURN IF ( AND ( currAttribute <> "Total Paid Time", currAttribute <> "Non Operating Time" ), DIVIDE ( CALCULATE ( AVERAGE ( 'Waterfall Table'[Value] ), VALUES ( 'Waterfall Table'[LineSK] ) ), total ) )

Regards,
Xiaoxin Sheng

topic How to fill each row of a measure based on a single row value? (DAX Measure for Waterfall Chart) in DAX Commands and Tips

How to fill each row of a measure based on a single row value? (DAX Measure for Waterfall Chart)

Re: How to fill each row of a measure based on a single row value? (DAX Measure for Waterfall Chart)