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
timurcakin
Helper I
Helper I

multiply column and a measure - wrong total

Hi There,

 

i am trying to multiply the total quantity of the items with the number of orders (frequency) but in second screenshot total is 190 instead of 86 (64+22) 

 

timurcakin_0-1674795397052.png

 

timurcakin_1-1674795559149.png

 

can you please help to fix that total, i have spent 2 days and couldnt figure out the error.

thanks in advance

best regards

tim

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

16 REPLIES 16
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file.


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

Hi Ashish,

i modified it a bit as it was connected to the database. 

TEST- Copy.pbix

but ultimately what i want to do is to divide "measure3" per line to grand total of "measure" (which is wrong or not what i want in the file) 

i want to see 31,214,080 instead of 5,221,978,332

 

timurcakin_1-1675030483613.png

hope that make sense

thanks in advance

timur

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

I've having this same issue.  Is there any way the formula can be shared since the PBIX file is no longer available?

Hi,

I do not have that file.  Share some data, explain the question and show the expected result.


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

I've having this same issue.  Is there any way the formula can be shared since the PBIX file is no longer available?

Hi Ashish,

 

Total is correct thank you, Is it possible to find weighted average of these?

Measure 2 per item / total measure 2

that would be Legendary!

 

I've having this same issue.  Is there any way the formula can be shared since the PBIX file is no longer available?

You are welcome.  If my previous reply helped, please mark that reply as Answer.  Try this measure

Measure 3 = DIVIDE([Measure 2],CALCULATE([Measure 2],ALL(Sheet3[No_])))

Hope this helps.

Untitled.png


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

Hi Ashish,

I will need one more thing, do i need to open a new post for it?

As this is a measure (and not a column) i am struggling to get the cumulative % of measure 2, is this something you can help with?

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

Absolute Legend!!

thank you very much Ashish

You are welcome.


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

I will need one more thing, do i need to open a new post for it?

As this is a measure (and not a column) i am struggling to get the cumulative % of measure 2, is this something you can help with?

vicky_
Super User
Super User

Technically, your measure is doing what you've asked it to do (multiply the value in the quantity by the value in the frequency), and this applies to the total row too. Of course, this isn't the result you want. Greg Deckler has a very in-depth write-up on how to solve this issue with measures. Link here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376/highlig...

Hi Vicky,

thank you very much for the guidance but the link is not giving a definite answer either.

let me ask this way, is there a way to:

1- multiply product quantity *  number of orders = frequency

2- take weighted average of products (based on frequency)

 

appreciate your feedback

 

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.