Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dhkhanna
New Member

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

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.

LineSKValuePaidTotalPaidTimeAttributeValueValueTotal
1  Non Operating Time122013.93%
1 7540Total Paid Time754086.07%
114 Maintenance14 
1138 Changeover138 
1160 Clean / Sanitation160 
1947.18 Other947.18 
16280.82 USLE6280.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 - 
LineSKValuePaidTotalPaidTimeAttributeValueValueTotal
527 7540Non Operating Time122013.93%
527 7540Total Paid Time754086.07%
5270.19%7540Maintenance14 
5271.83%7540Changeover138 
5272.12%7540Clean / Sanitation160 
52712.56%7540Other947.18 
52783.30%7540USLE6280.82 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.