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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculation assistance with DAX

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 

8 REPLIES 8
Kevin_Harper
Helper I
Helper I

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
Not applicable

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.

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
Not applicable

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 2160image.png

 

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

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.

@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] )
            )
    )
)

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

can you tell me whats going wrong over here :

 

Anonymous
Not applicable

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

 

query.png

 

 

 

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

 

image.png

 

Anonymous
Not applicable

can anyone help me with this???

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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