Calculation assistance with DAX

[Anonymous]

Hi,

Need a assistance i have attached sheet from which i need the number of  1080 with DAX. sample is attached in below link

https://we.tl/t-df1Fr6BUvN

@dax 

[Kevin_Harper]

Can you be more specific on what you want?  I see from your excel file that you have a "Grand Total" row that is summing the values in the "Target Mandays" column.

Is this the value you are looking for?  Are you saying you need help with a DAX formula to recreate this value in a Power BI file?

[Anonymous]

So i have 2 sheets
1- is of sales data
2- is of headcount sheet which tells me how many sales promoters are need to be deployed on specific brand on each day

So, i need to perform a calculation on sales data that like:
1- how many dates are there in sales data on which brand sales promoters have worked
E.g brand mlb has 7 working days (distinct count of days)
Now i need to multiply 7 working days with for mlb brand and lookup in to head count sheet how many headcount is need to be deployed which is 6 so (7*6)=42

And this calculation will goes on for brand in every region.

[Kevin_Harper]

It looks like you have all of the measures completed already.  I just created a pivot table and added the columns you have selected for each region by brand.  Is this what you are looking for?  See sheet 2 with pivot table.

https://we.tl/t-50OQtnaHbq

[Anonymous]

what it is showing is the number of 2160 which is incorrect, it is showing other regions as well due to which it is calculating 2160

1080 number is correct which i have calculated manually in sheet 6.

So what i am looking that it should skip those regions who doesnt have the data in the sales sheet or show Zero working days if there is no working done.

[v-chuncz-msft]

@Anonymous 

You may add the measure below.

Measure =
SUMX (
    SUMMARIZE ( Sheet1, Sheet1[Region Name], Sheet1[Brand] ),
    CALCULATE (
        DISTINCTCOUNT ( Sheet1[Interaction Date] )
            * LOOKUPVALUE (
                Sheet2[Head Count],
                Sheet2[Region Name], SELECTEDVALUE ( Sheet1[Region Name] ),
                Sheet2[Brand], SELECTEDVALUE ( Sheet1[Brand] )
            )
    )
)

[Anonymous]

can you tell me whats going wrong over here :

@v-chuncz-msft 

[Anonymous]

Nope, even after adding this measure it doesnt calculate correctly. 834 it is showing in total :

 the correct values are highlighted with green and the total of the measure is also different 

[Anonymous]

can anyone help me with this???