divide function / measure provides wrong values

[td1n1z]

Hi, 

I'm trying to do get a cost per item but divide function is giving wrong results.

I have an ID bridge table and added the dim[ID] columns and created a Many(dim) to One(bridge) relationship, also Many(fact) to One(bridge) relationship. 
DIVIDE function: = DIVIDE(sum(fact[paid]), sum(dim1[quantity]) but instead of filtering only the ID of dim1 table, the measure sums up all rows of fact[paid].

Another measure:

DIVIDE(

    CALCULATE(

        SUM(fact[paid]),

        'fact'[ID] in VALUES('dim1'[ID])

    ),

    CALCULATE(

        SUM('dim1'[quantity]),

        'dim1'[ID] IN VALUES(fact[ID])

    )

)

but this gives me a different value than when i calculate by hand.

EDIT sample data:

FACT table

ID	PAID
123/abc/Madzucane	2351567,24
34/abc/Namojelia	6587692,38
34/abc/Kuekue	5385395,67
567/abc/Mulequiua	9546954,17
567/abc/Muanona	0
567/abc/Namaponda	3145057,08
567/abc/Mucuali	0
567/abc/Jagoma	1474251,8
567/abc/Hapacue	1224179,88
111/abc/Nhaunama	9562338732358,00
111/abc/Mecundanha	2351567,24
111/abc/Bofana	6587692,38
111/abc/Chizine	5385395,67
111/abc/Chigoza	9546954,17
111/abc/Dombe	1474251,8
111/abc/Mugugu	1224179,88
111/abc/Chitare	9562338732358,00
111/abc/ Zimbire	2351567,24
111/abc/Nhacafula	3145057,08
111/abc/Buzua	51847,09
678/abc/Chidzolomondo	15120847,09
678/abc/Luia	0
678/abc/Mazoe	0
678/abc/Boroma	8636267,66
678/abc/222angoma	1614816
678/abc/Domue	4209810,36
678/abc/Kambulatsitsi	5872842,61
678/abc/Cantina Oliveira	1364370
144/abc/Guara-Guara	4150305,77
144/abc/222amitanga	3653738,93
144/abc/Tica	0
144/abc/Muanza	0
144/abc/Canxixe	5166245,35
111/abc/Mats222o	3316500,5
111/abc/Chitunga	2461247,46
222/abc/Pambara	2471193
222/abc/Mahundzulucane	17672213
222/abc/Save	31192833,86
222/abc/Mahave	15996108,7
222/abc/Cumbana	103174794,75
222/abc/Chitondo Sul	17027567,9
222/abc/Muatimamba	14827759,84
222/abc/Sitila	18053852,05
567/abc/Mica Sede	5397425,38
567/abc/ Pavala	9406638,62
567/abc/Baixo Pinda	11457267,18
444/abc/Nsime	6818858,53
444/abc/Massango	20282448,56
444/abc/Pontia	18931890,32
444/abc/Bondoia	21121554,62
444/abc/Muzele	12157453,07
444/abc/Bedjile	6818858,53
444/abc/25 de Junho	20282448,56
444/abc/Mahubo	18931890,32
444/abc/Goba	5397425,38
444/abc/Calanga	9406638,62
444/abc/Panjane	11457267,18
444/abc/3 Fevereiro	6818858,53
444/abc/Possulane	20282448,56
444/abc/Gumbane	18931890,32
444/abc/Maluana	21121554,62
222/abc/Sahane	12157453,07
222/abc/Tsenane	12157453,07
222/abc/Macuacua	12157453,07
222/abc/Machame	10371,16
678/abc/Nguni	20282448,56
678/abc/Chioco	18931890,32
678/abc/Goba	997119,16
678/abc/Chazia	10371,16
678/abc/Tiago	99031,97
678/abc/Muze	183112,49
678/abc/Samala	22815092,64
678/abc/Mememe 2	5805380,48
678/abc/Depete	12319,16
454/abc/Mussaraua	0
454/abc/Murrua	0
454/abc/Nante	1140371,16
454/abc/Mocubela	18899031,97
454/abc/Micaune	18306112,49
454/abc/Gonhane	22815092,64
454/abc/Sup222o	5805380,48
454/abc/Sabe	12411319,16
454/abc/Lua-Lua	10466003,17
454/abc/Namitangurine	7808677,83
454/abc/Regone
567/abc/Mulequiua	9546954,17
454/abc/Magodone	1140371,16
454/abc/Nhanfuba	580564380,48
454/abc/Nerire	305380,48
454/abc/Munhonha	123805380,48
454/abc/Chimbazo	75380,18
454/abc/Socov222o	58080,40
454/abc/Nante	1140371,16
111/abc/Dombe	76611419,66
165/abc/Nzinzi	5013568
123/abc/111ingueningue	411319,10
123/abc/Maluluane	12411,16
123/abc/Candiza	174319,16
123/abc/Z222ane	91249,16
678/abc/Zobue	10022904,76
567/abc/Namaponda	3145057,08
987/abc/Regone	13160094,25
123/abc/Malehice	24167589,65
34/abc/Meloco	8776650,68

dim1 table:

ID	quantity
567/abc/Mulequiua	5
454/abc/Nante	9
444/abc/Possulane	5
454/abc/Nerire	4
454/abc/Magodone	4
454/abc/Munhonha	4
454/abc/Socovinho	4
454/abc/Chimbazo	4
454/abc/Nhafuba	4
111/abc/Dombe	4

Thanks in advance for any info and suggestions.

[v-nuoc-msft · Accepted solution]

Hi @td1n1z

For your question, here is the method I provided:

Here's some dummy data

"Dim"

"FACT"

Create a relationship between your two tables based on the 'ID' field.

Create a measure.

DIVIDE FUNCTION = DIVIDE(
        CALCULATE(
            SUM('FACT'[PAID]),
            'FACT'[ID] IN VALUES('Dim'[ID]) && 'FACT'[ID] = MAX('FACT'[ID])
        ), 
        CALCULATE(
            SUM(Dim[quantity]), 
            'Dim'[ID] IN VALUES('FACT'[ID])
        )
    )

Here is the result.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[td1n1z]

Firstly thank you very much for taking the time to answer my question.
What I found was that, I am almost embarrased to say this but, my fact table had a duplicate entry, and I was always checking my fact against my bridge table (which didn't have said duplicate).
Your solution gives me the same values I had with my measure so the oversight was on my end.
I'm gonna accept this as the correct answer. 
THanks you very much once more.

[Ritaf1983]

Hi @td1n1z 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly