Dax function recreate

[Junaid11]

Hello,

I had this data below:

Date	SalesOrder	Revenue	Department
11/02/2020	A1	25210	P
11/02/2020	A2	56732	L
11/02/2020	A3	45627	L
12/02/2020	A1	25210	P
12/02/2020	A2	71354	L
12/02/2020	A3	43362	P
12/02/2020	A4	23565	P
12/02/2020	A4	5743	P

I used below formula to calculate the measure showing New sales order:

Measure =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table 1'[Date] ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[SalesOrder] )
    ) = 1,
    SUM ( 'Table 1'[Revenue] )
)

It was generating below result which was perfect for me:

it was working fine until I put Department column in the row. The Dax value did not show any value. It was showing it blank but whenever I clicked the department the sales order value was showing result.

It is showing it like below:

but I want department to show full value as well like P has new value in sales order A4 so Department value should show the sum of ne values of any new sales order it has in it.

Is it possible to achieve it?

It was solved previously by @smpa01 

[Pragati11]

Hi @Junaid11 ,

Try modifying the DAX as follows:

Measure =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table 1'[Date] ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[SalesOrder], 'Table 1'[Department] )
    ) = 1,
    SUM ( 'Table 1'[Revenue] )
)

Thanks,

Pragati

[Junaid11]

Hello @Pragati11 ,

I have already done that but it is still not working.

[v-chenwuz-msft]

Hi @Junaid11 ,

From your description, you should want to use a gong'neng similar to this function:

HASONEVALUE(<columnName>)

Try this code:

Measure = 
IF(
    HASONEVALUE( 'Table'[Department] ),
    SUM( 'Table'[Revenue] ),
    BLANK()
)

result:

Best Regards

Community Support Team _ chenwu zhu

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