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
omarevp
Helper II
Helper II

Total measure, made by measures

Hi guys! I'm really grateful about the help you are always giving us!

 

I've got a question for you, this has been killing me for a while.

 

I have a "Purchases" table, in whic I want to know the "price" of every item I buy. This is possible by making this simple measure:
SUM(table[import]) / SUM(table[Quantity]). Divide the sum of the purchase with the quantity of that item bought. Now, I'm trying to compare "Price this month" with "Price last month" in which I ASUME that, I need to make two different measures to know that:

 

Price this month = CALCULATE ( SUM(table[import]) / SUM([Quantity]), CalendarTable[Relative_Month]="This month")

Price last month = CALCULATE ( SUM(table[import]) / SUM([Quantity]), CalendarTable[Relative_Month]="Last month")

 

Now, the next thing I would LOVE to know is "Impact" between those prices, I mean, I need to calculate


Impact = Price this month - Price last month * SUM(table[Quantity])

 

It works fine in a table visual, because in table, it is showed by item. And if I export that data to a CSV file, and I do the sum by myself, it shows the correct result, here the result in excel: -731070.8722 (This is the correct one)

 

 

BUT, when I see the TOTAL at the end of the visual it is wrong, and I need it as a Card Visual, so when I put that measure in a Card Visual it shows the wrong result and I have no way to use different filters on it. Here, the current result (the wrong one):

Captura.PNG

I need that result in a card visual 😞


Another comments=

- Sometimes, "price this month" or "price last month" is in blank.

- I already tried applying this measure:

 

c_Impacto real =

VAR __table = SUMMARIZE(table,table[item],"__value",[Impact])

RETURN

IF(HASONEVALUE(table[item]),[Impact],SUMX(__table,[__value]))

 

Hello, @Greg_Deckler, I know you have been a lot in this kind of issues, I've tried with your measure for Total, but still doesnt work. I´m pretty sure you can help me out. 😃

 

I'd really appriciate your help, or another's help, this been killing me for a big while.

 

THANK YOU SO MUCH!

 

 Omar

2 ACCEPTED SOLUTIONS

@omarevp - OK, I believe that I have solved this. Sorry it took me a bit to get around to it. I created a new measure called "c_Impacto real GJD". This works in a Card visual because it replicates all of the filters that you have on your table. See attached.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

 Hi @Greg_Deckler!!!

 

You're a Genious!!! It worked perfectly! So thanks a lot! I also took note and analyzed the measure, I didn't know we can do something like that with DAX. If there is something I can do for you, would be a placer!

 

Keep on rocking man! THANKS!

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

Any way you can share that data you presented in the example as text that can be copied and pasted? So much easier to troubleshoot if it is easy to recreate. Even a subset of it should work. I assume that all of the formulas you shared are measures?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_DecklerThanks for your quick answer.

 

Yes, all of them are measures made from the table. In this link you will find a subset taken from the table:

 

https://drive.google.com/file/d/1nJ1qPR5w_wb3olMFVBYXlf1rKhTDLUHe/view?usp=sharing. It has the main fields you will need.

 

Thanks a lot!

OK, I loaded up your data and wrote the measures, seems like I am getting the same number when I export to Excel versus the number I get as the total of c_IR.

 

See Page 16, measure_data table.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks, @Greg_Deckler..

 

I want to share my pbix with you to show you what i'm trying to do.. I know its really messy, but if you export my table, it gives you another whole different number, because i have some filters applied in the table, as you can see..

 

After exporting, the result in the excel file for Impact is the good one..

 

https://drive.google.com/file/d/1XJMmR8q4cPq5lm9-MYJByXwCOdPwDR9C/view?usp=sharing

 

I know this could be a little annoying, but im really gratefull with your help!

 

OK, I am taking a look at it. If I put "c_Impacto real" into your table I get the same total as if I export it. But, if I use it in a card visual, I don't get the same answer as the Total in the table. Is that the issue?

 

If so, I think it lies in the fact that you are doing some additional visual level filters on that table. You would need to apply those same filters to the table that you do your SUMX over. So, essentially when you take c_Impacto real out of your table, you lose all of those filters, which affects your calculation.

 

What I don't understand is why you have Impacto in your table. For the rows it gives all the same values as "c_Impacto real" but the totals are different and while c_Impacto real matches excel, your Impacto does not so something funky going on with the Impacto measure. You could probably fix that by creating a measure just like c_Impacto real that does a SUMX across a table in which you have put Impacto (versus c_Impacto in your Impacto real measure.

 

I am going to work on a version of your c_Impacto real measure that includes the filters in your visual. That should fix it.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you @Greg_Deckler, that is the issue, when I take my c_impacto real measure out of the table, it loses all the visual level filter, so the real number I need is what excel provides, by exporting the table, with those filters applied.

 

My final result should be a Card Visual which includes c_real_impacto with the same result excel provides.

 

NOTE: Thank you for this: "I am going to work on a version of your c_Impacto real measure that includes the filters in your visual. That should fix it."

 

Ive been trying a lot of different measures, so I hope they are not mixed up so you can see it clearly. Thank you Greg.

@omarevp - OK, I believe that I have solved this. Sorry it took me a bit to get around to it. I created a new measure called "c_Impacto real GJD". This works in a Card visual because it replicates all of the filters that you have on your table. See attached.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

 Hi @Greg_Deckler!!!

 

You're a Genious!!! It worked perfectly! So thanks a lot! I also took note and analyzed the measure, I didn't know we can do something like that with DAX. If there is something I can do for you, would be a placer!

 

Keep on rocking man! THANKS!

Thanks @omarevp, always happy to help! DAX is an incredibly powerful language once you really start to get the hang of it!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.