Trouble with a measure

---

Ashish_Mathur wrote:

Hi,

 

Try this

 

=If(HASONEVALUE(Indfortoldning[Løbenummer]),AVERAGE(Indfortoldning[Moms grundlag]),SUMX(SUMMARIZE(Indfortoldning,Indfortoldning[Løbenummer],"ABCD",AVERAGE(Indfortoldning[Moms grundlag])),[ABCD]))

 

Hope this helps.

---

Hey there,

Thanks a ton for the above measure, was just what I wanted.

Now I have however stumpled into another issue I hadnt seen coming so I need this measure changed up a bit to work the same way qlikview does. I will do my best to explain the issue but please ask if there is any questions:

So the above measure does the correct, what I hadnt realized was there can be multiple different invoices with the same "løbenummer" and different "Moms grundlag" so if that is the case I want the value to be blank. Lets say we have the below scenario

Invoice 1 

Løbenummer 123

Moms grundlag 999

Invoice 2 

Løbenummer 123

Moms grundlag 123

The above 2 invoices has same løbenummer but different momsgrundlag, if that is the case I dont want it to take the average or top 1 (both should give same result) I want the value to become blank as if there isnt typed anything into the "moms grundlag" field.

My entire issue is I am doing an SQL query and 1 invoice can have multiple lineitems, so even if "Moms grundlag" is supposed to only have 1 value pr invoice it spits it out as many times as there is lineitems. Qlikview simply take either the average or the top 1 of this value (aint sure how it does it) and in qlikview it can somehow differentiate between two invoices with the same løbenummer, i guess it just makes moms grundlag blank if the same løbenummer exists multiple times but with different moms grundlag.

I hope the above made sense.