Help with Row Display

[jnaugle]

In the above table I would like to not have the QTY on SO duplicated in each row just display the total. Is there away for me to do this?

 Looks like when I have the filter set then it displays the way I need. 

[v-angzheng-msft]

Could you please consdier sharing more details about it or a simple sample file without any sesentive information for further discussion? Thank you.

How to provide sample data in the Power BI Forum 

[v-angzheng-msft]

Hi, @jnaugle 

May I ask if your problem has been solved? Is the above answer helpful to you?
If it helps, could you please mark the response which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

[DataInsights]

@jnaugle,

You can use the HASONEVALUE function. Since the total row has more than one Item Location, this measure will display the quantity for only the total row:

QTY SO = IF ( NOT HASONEVALUE ( Table[Item Location] ), SUM ( Table[QTY on SO] ) )

[jnaugle]

The only issue with this solution is that once the user applies the location filter the SO Quantity is not populated

Any Ideas?

[DataInsights]

@jnaugle,

Try this measure:

QTY SO = 
VAR vLocationCountAll =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Table'[Item Location] ),
            ALL ( 'Table'[Item Location] )
        )
    )
VAR vLocationCountSlicer =
    COUNTROWS ( ALLSELECTED ( 'Table'[Item Location] ) )
VAR vHasSlicerSelection =
    IF ( vLocationCountAll = vLocationCountSlicer, 0, 1 )
VAR vResult =
    SWITCH (
        vHasSlicerSelection,
        1, SUM ( 'Table'[QTY on SO] ),
        0, IF ( NOT HASONEVALUE ( 'Table'[Item Location] ), MAX ( 'Table'[QTY on SO] ) )
    )
RETURN
    vResult

--------------------------------------------------------------

[jnaugle]

still duplicates when no filters applied.  Maybe I missed something on the Dax.

QTY SO =

VAR vLocationCountAll =

COUNTROWS (

CALCULATETABLE (

VALUES ( ItemLedgerEntries[Item Location] ),

ALL ( 'ItemLedgerEntries'[Item Location] )

)

)

VAR vLocationCountSlicer =

COUNTROWS ( ALLSELECTED ( 'ItemLedgerEntries'[Item Location] ) )

VAR vHasSlicerSelection =

IF ( vLocationCountAll = vLocationCountSlicer, 0, 1 )

VAR vResult =

SWITCH (

vHasSlicerSelection,

1, SUM ( Sales_Order_Line[Quantity] ),

0, IF ( NOT HASONEVALUE ( 'ItemLedgerEntries'[Item Location] ), MAX ( 'Sales_Order_Line'[Quantity]] ) )

)

RETURN

vResult

[jnaugle]

I have this error

[jnaugle]

ok so now dax errors at this point but the result is still the same .  Duplicate data in the rows

[DataInsights]

@jnaugle,

I would need to see your data model. Can you provide a link to your report? You can provide sample data that illustrates the issue.