Forum Discussion
Post ProdigyMeasure with threshold in fact table
Hello,
I have two tables with some money column (MoneyTable[MoneyColumn], MoneyTable2[MoneyColumn]) and a fact table with Ratio in percentages
I want to divide sum of these, but only for lines from Fact table, where column in Fact table [Ratio] is above 150% (thus 1.5)
My measures which looks ok at first sight (calculate divide with sum and filter on [Ratio] <= 1.5) doesnt work
Connection is between ID of fact table and ID of MoneyTable
Hi,
Yeah I figured it out but since the values are not stored in fact table, the filters didn't work on that since it replicates filter from one side to many side. Anyway thank you for your response !
6 Replies
v-yalanwu-msftCommunity Support
Hi, Pbiuserr ;
You could create a measure as follow:
Measure = CALCULATE(SUM('Money table1'[Amount])/SUM('MoneyTable 2'[Amount]),FILTER('Fact table',[Ratio]<1.5))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PbiuserrHi,
Yeah I figured it out but since the values are not stored in fact table, the filters didn't work on that since it replicates filter from one side to many side. Anyway thank you for your response !
- Greg_Deckler
Community Champion
Pbiuserr Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.- Pbiuserr
Fact table
ID Ratio Grade ID1 100% Grade1 ID2 200% Grade2 ID3 50% Grade3 Money table1
ID Amount ID1 100 ID2 1000 ID3 200 MoneyTable 2
Grade Amount Grade1 50 Grade2 1000 Grade3 50 Expected result
(100+200) / (50+50) = 300/100 = 3
Ratio is a threshold - I need to sum lines from MoneyTable1 and MoneyTable2 only if the line on threshold is under or equals to 1.5 (150%). Is it even possible in that scenario?
- Greg_Deckler
Pbiuserr Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. - v-yalanwu-msft
Hi, Pbiuserr ;
It's nice to hear that you solved the problem.can you kindly mark the proper reply as a solution to close the case?
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
