How to fix display TOTAL in x-axis question

[SelflearningBi]

Please help out how to fix these dax functions. I would like to display Total of average (price1 vs price 2) by brand level in the last column.  Somehow if any brands price 1 or price 2 is empty, the graphic automality displays total avergae for that brand.  I just want to display nothing insteand of total average 10.8 as I highlighted in circle. 

1 =

var amount = average('Quotation Dynamic_Page'[FOB_Price])

return if(ISBLANK(amount), CALCULATE(AVERAGE('Quotation Dynamic_Page'[FOB_Price]), ALLEXCEPT('Quotation',Quotation[Brand] )),amount)

2 =

var amount = average(Quotation[Target_Price_From])

return if(ISBLANK(amount),CALCULATE(AVERAGE(Quotation[Target_Price_From]), ALLEXCEPT('quotation',Quotation[Brand] )),amount)

[v-stephen-msft · Accepted solution]

Hi @SelflearningBi ,

Is your Total column a new column? Below is my solution.

1.Create a new calculated table to use your brand column and add a total value.

ChartAxis = UNION ( VALUES ('Table'[Brand] ), ROW ( "Brand", "Total" ) )

2.Create two measures.

Measure1 =
IF (
    MAX ( 'ChartAxis'[Brand] ) = "Total",
    CALCULATE ( AVERAGE ( 'Table'[Price1] ), ALLEXCEPT ( 'Table', 'Table'[Brand] ) ),
    CALCULATE (
        SUM ( 'Table'[Price1] ),
        FILTER ( 'Table', [Brand] = MAX ( 'ChartAxis'[Brand] ) )
    )
)

Measure2 =
IF (
    MAX ( 'ChartAxis'[Brand] ) = "Total",
    CALCULATE ( AVERAGE ( 'Table'[Price2] ), ALLEXCEPT ( 'Table', 'Table'[Brand] ) ),
    CALCULATE (
        SUM ( 'Table'[Price2] ),
        FILTER ( 'Table', [Brand] = MAX ( 'ChartAxis'[Brand] ) )
    )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SelflearningBi]

@v-stephen-msft Hi thank you for helping, i was wondeing what if my price1 and price 2 is a measure.  for example price 1 is percentage of On Time Shipped Quantity and Price 2 is percentage of ON TIME shipped Value.  In your formula Calculate (Average( ) ),,, only avaiable for column. is it possible also work for Measure? 

[FarhanAhmed]

your DAX statement is saying itself "If it is your average of single brand is BLANK then it is calculating average of all the brands"

I think you are trying to calculate average for totals and you need to put statement like this

Old: if(ISBLANK(amount), CALCULATE(AVERAGE('Quotation Dynamic_Page'[FOB_Price]), ALLEXCEPT('Quotation',Quotation[Brand] )),amount)

New One:

if(ISBLANK(amount) && NOT (HASONEVALUE(Quotation[Brand]), CALCULATE(AVERAGE('Quotation Dynamic_Page'[FOB_Price]), ALLEXCEPT('Quotation',Quotation[Brand] )),amount)

[SelflearningBi]

@FarhanAhmed Hi thank you for helping. I try your formula but still doesnt work. It still shows the average of totals for those blank brand. 

2 =

var amount = average(Quotation[Target_Price_From])

RETURN if(ISBLANK(amount)&& NOT(HASONEVALUE(Quotation[Brand])),CALCULATE(AVERAGE(Quotation[Target_Price_From]), ALLEXCEPT('Quotation',Quotation[Brand] )),amount)

[FarhanAhmed]

Can you please share the sample data of your problem. Try this too

if(ISBLANK(amount), BLANK(),if(ISBLANK(amount) && NOT (HASONEVALUE(Quotation[Brand]), CALCULATE(AVERAGE('Quotation Dynamic_Page'[FOB_Price]), ALLEXCEPT('Quotation',Quotation[Brand] )),amount))