Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have loaded data into power BI from excel and I have created relationship betweeen tables using currency column.
Sheet 1
Sheet 2
Now I have following measures
Any suggestions to solve this ?!
Thanks in advance
@Ashish_Mathur (May be can you help me regarding this post!)
Solved! Go to Solution.
Hi,
PBI file attached.
Hope this helps.
Hi,
Share the download link of the PBI file with your measures already written. Show the problem and the expected result.
Hello @Ashish_Mathur ,
FYI, In the loop of this topic, you can see I already had conversation with other community members but I coudn't able to solve the problem.
Sheet 1:
Material | Date | Quantity | Price | Currency |
X1 | 01.01.2024 | 30 | 86 | EUR |
X2 | 01.01.2024 | 20 | 177 | USD |
X3 | 01.01.2024 | 15 | 36 | EUR |
X1 | 01.02.2024 | 43 | 81 | EUR |
X2 | 01.02.2024 | 46 | 165 | USD |
X3 | 01.02.2024 | 34 | 40 | EUR |
X1 | 01.03.2024 | 28 | 85 | EUR |
X2 | 01.03.2024 | 46 | 175 | USD |
X3 | 01.03.2024 | 32 | 35 | EUR |
Sheet 2 :
From € to $ (Conv. Rate) | Currency |
1,08 | USD |
1 | EUR |
Both this tables are in relationship by column Currency.
Measures:
That does not give me what i requested for in my previuos message.
Here are details you requested.
Hello @Ashish_Mathur ,
Sheet 1:
Material | Date | Quantity | Price | Currency |
X1 | 01.01.2024 | 30 | 86 | EUR |
X2 | 01.01.2024 | 20 | 177 | USD |
X3 | 01.01.2024 | 15 | 36 | EUR |
X1 | 01.02.2024 | 43 | 81 | EUR |
X2 | 01.02.2024 | 46 | 165 | USD |
X3 | 01.02.2024 | 34 | 40 | EUR |
X1 | 01.03.2024 | 28 | 85 | EUR |
X2 | 01.03.2024 | 46 | 175 | USD |
X3 | 01.03.2024 | 32 | 35 | EUR |
Sheet 2 :
From € to $ (Conv. Rate) | Currency |
1,08 | USD |
1 | EUR |
Both this tables are in relationship by column Currency.
Measures:
Hi @Ashish_Mathur ,
I have updated the link.
https://drive.google.com/file/d/1tanNNwqShDfjB4L7AID--7MuPl52GBny/view?usp=sharing
Access Denied message.
@The8 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: 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
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
@Greg_Deckler I have already gone through your post and videos but I coudn't able to solve the problem. I coudn't able to get your solution methodology exactly in my scenario.
That would be great if have a solution exactly according to my data.
Thanks !!
@The8 Cool, please post your data as text and all relevant formulas.
Hello @Greg_Deckler ,
Sheet 1
Material | Date | Quantity | Price | Currency |
X1 | 01.01.2024 | 30 | 86 | EUR |
X2 | 01.01.2024 | 20 | 177 | USD |
X3 | 01.01.2024 | 15 | 36 | EUR |
X1 | 01.02.2024 | 43 | 81 | EUR |
X2 | 01.02.2024 | 46 | 165 | USD |
X3 | 01.02.2024 | 34 | 40 | EUR |
X1 | 01.03.2024 | 28 | 85 | EUR |
X2 | 01.03.2024 | 46 | 175 | USD |
X3 | 01.03.2024 | 32 | 35 | EUR |
Sheet 2
From € to $ (Conv. Rate) | Currency |
1,08 | USD |
1 | EUR |
Both this tables are in relationship by column Currency.
Measures:
@The8 See PBIX attached below signature.
Hello @Greg_Deckler
Is that possible even when I take material into a table or matrix in my case?
Thanks !!
@The8 I don't think that you are taking the time to read up on this issue, particular the second link I posted that goes through the issue in detail and how to fix it. If you include additional columns in your table visual, you have to add those columns to your SUMMARIZE like this:
Final Price Total =
VAR __Table = SUMMARIZE( 'Sheet1', [Date],[Material], "__Value", [Final Price in €])
VAR __Result = IF(HASONEVALUE(Sheet1[Date]), [Final Price in €], SUMX(__Table, [__Value]))
RETURN
__Result
Hi @Greg_Deckler
I have gone through your posts and I have also tried DAX with material but I got error even though.
Modified Measure I used is
@The8 Actually the relationship direction needed to be both and not single direction and that was throwing the numbers off. See updated file.
Hi @Greg_Deckler
Thank you, Good day!
You are exactly right, when we change relationship direction to "both" and adding Material into my measure as below is working absolutely fine even with Material Slicer.
But when I make slicer selection based on date again I have same problem, totals being wrong as below image for your reference.
With Date SLicer Selection:
Any changes needs to be done still ?
@The8 That would be:
Hi @Greg_Deckler
Thanks for your update.
But I still have the save problem with incorrect totals.
Measure I used
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |