Forum Discussion

td1n1z's avatar
td1n1z
Regular Visitor
2 years ago
Solved

divide function / measure provides wrong values

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
IDPAID
123/abc/Madzucane2351567,24
34/abc/Namojelia6587692,38
34/abc/Kuekue5385395,67
567/abc/Mulequiua9546954,17
567/abc/Muanona0
567/abc/Namaponda3145057,08
567/abc/Mucuali0
567/abc/Jagoma1474251,8
567/abc/Hapacue1224179,88
111/abc/Nhaunama9562338732358,00
111/abc/Mecundanha2351567,24
111/abc/Bofana6587692,38
111/abc/Chizine5385395,67
111/abc/Chigoza9546954,17
111/abc/Dombe1474251,8
111/abc/Mugugu1224179,88
111/abc/Chitare9562338732358,00
111/abc/ Zimbire2351567,24
111/abc/Nhacafula3145057,08
111/abc/Buzua51847,09
678/abc/Chidzolomondo15120847,09
678/abc/Luia0
678/abc/Mazoe0
678/abc/Boroma8636267,66
678/abc/222angoma1614816
678/abc/Domue4209810,36
678/abc/Kambulatsitsi5872842,61
678/abc/Cantina Oliveira1364370
144/abc/Guara-Guara4150305,77
144/abc/222amitanga3653738,93
144/abc/Tica0
144/abc/Muanza0
144/abc/Canxixe5166245,35
111/abc/Mats222o3316500,5
111/abc/Chitunga2461247,46
222/abc/Pambara2471193
222/abc/Mahundzulucane17672213
222/abc/Save31192833,86
222/abc/Mahave15996108,7
222/abc/Cumbana103174794,75
222/abc/Chitondo Sul17027567,9
222/abc/Muatimamba14827759,84
222/abc/Sitila18053852,05
567/abc/Mica Sede5397425,38
567/abc/ Pavala9406638,62
567/abc/Baixo Pinda11457267,18
444/abc/Nsime6818858,53
444/abc/Massango20282448,56
444/abc/Pontia18931890,32
444/abc/Bondoia21121554,62
444/abc/Muzele12157453,07
444/abc/Bedjile6818858,53
444/abc/25 de Junho20282448,56
444/abc/Mahubo18931890,32
444/abc/Goba5397425,38
444/abc/Calanga9406638,62
444/abc/Panjane11457267,18
444/abc/3 Fevereiro6818858,53
444/abc/Possulane20282448,56
444/abc/Gumbane18931890,32
444/abc/Maluana21121554,62
222/abc/Sahane12157453,07
222/abc/Tsenane12157453,07
222/abc/Macuacua12157453,07
222/abc/Machame10371,16
678/abc/Nguni20282448,56
678/abc/Chioco18931890,32
678/abc/Goba997119,16
678/abc/Chazia10371,16
678/abc/Tiago99031,97
678/abc/Muze183112,49
678/abc/Samala22815092,64
678/abc/Mememe 25805380,48
678/abc/Depete12319,16
454/abc/Mussaraua0
454/abc/Murrua0
454/abc/Nante1140371,16
454/abc/Mocubela18899031,97
454/abc/Micaune18306112,49
454/abc/Gonhane22815092,64
454/abc/Sup222o5805380,48
454/abc/Sabe12411319,16
454/abc/Lua-Lua10466003,17
454/abc/Namitangurine7808677,83
454/abc/Regone 
567/abc/Mulequiua9546954,17
454/abc/Magodone1140371,16
454/abc/Nhanfuba580564380,48
454/abc/Nerire305380,48
454/abc/Munhonha123805380,48
454/abc/Chimbazo75380,18
454/abc/Socov222o58080,40
454/abc/Nante1140371,16
111/abc/Dombe76611419,66
165/abc/Nzinzi5013568
123/abc/111ingueningue411319,10
123/abc/Maluluane12411,16
123/abc/Candiza174319,16
123/abc/Z222ane91249,16
678/abc/Zobue10022904,76
567/abc/Namaponda3145057,08
987/abc/Regone13160094,25
123/abc/Malehice24167589,65
34/abc/Meloco8776650,68


dim1 table:

IDquantity
567/abc/Mulequiua5
454/abc/Nante9
444/abc/Possulane5
454/abc/Nerire4
454/abc/Magodone4
454/abc/Munhonha4
454/abc/Socovinho4
454/abc/Chimbazo4
454/abc/Nhafuba4
111/abc/Dombe4
 

Thanks in advance for any info and suggestions.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

     

     

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      td1n1z
      Regular Visitor
      2 years ago

      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.