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

Average and sum Dax

Hi All,

I have issues with to get the Divide and Sum Function. Please look into the sample model below

Table

Mat             code           Sales 

12              a1                  10

12              a1                  20

12              a1                  30

12              b1                  40

12              b1                  50

12              b1                  50

13              c1                   70

13              c1                   80

13              d1                   90

13              d1                  100

 

 

Result Table
Mat          Sales   

12              100              Logic :(10+20+30+40+50+50)/2( distinct count of "Code",   i.e. a1 & b1)

13              170     

Total        270

 

i tried to do sum of averages here, but getting a different result in total


Hope im making sense here

 

Thanks in advance !

Vinay

                     

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Write these measures:

Total Sales = SUM(Data[Sales])

Number of codes = DISTINCTCOUNT(Data[code])

Measure = SUMX(VALUES(Data[Mat]),[Total sales]/[Number of codes])

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Write these measures:

Total Sales = SUM(Data[Sales])

Number of codes = DISTINCTCOUNT(Data[code])

Measure = SUMX(VALUES(Data[Mat]),[Total sales]/[Number of codes])

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks @Ashish_Mathur  @v-joesh-msft 

 

Regards,

vinay

You are welcome.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-joesh-msft
Solution Sage
Solution Sage

Hi @Anonymous ,

You can try the measure below:

Measure =
VAR dis_code =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[ code] ) )
RETURN
    SUMX ( VALUES ( 'Table'[Mat] ), DIVIDE ( SUM ( 'Table'[Sales] ), dis_code ) )

Results are as follows:

11.PNG

 

Best Regards,

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

Anonymous
Not applicable

@v-joesh-msft  Thanks !

Its works fine here in sample model, but not working in my model where in i have 20K Mat &  Codes

I'm not understanding where have i gone wrong

 

Regards,

Vinay

Hi @Anonymous ,

Sorry, I made a mistake, you need to create two measures:

aver = DIVIDE(SUM('Table'[Sales]),DISTINCTCOUNT('Table'[ code]))
dis_code = SUMX(VALUES('Table'[Mat]),[aver])

In fact, this looks like a measure totals problem. Very common. See this post about it

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

 

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVqLo7_YxpJEncv4Qp7_c7gBY9BGKKnSvWRoC-OkrBzsmQ?e=6dW88D

Best Regards,

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

 

mauriciosotero
Resolver III
Resolver III

Hi,

 

I am not sure if I understand. Can you post the result you want?

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.