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

[dhkhanna]

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

[Anonymous · Accepted solution]

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