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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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):

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
Super User

@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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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!

9 REPLIES 9
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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

@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!

Super User

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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!

Super User

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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.

Super User

@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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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!

Super User

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors