Forum Discussion
Need help with a measure
Hello fellow users of Power Bi,
Well ill try to describe one of my issues:
=If(HASONEVALUE(Indfortoldning[Løbenummer]),AVERAGE(Indfortoldning[Moms grundlag]),SUMX(SUMMARIZE(Indfortoldning,Indfortoldning[Løbenummer],"ABCD",AVERAGE(Indfortoldning[Moms grundlag])),[ABCD]))
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 have uploaded some sample data here and hopefully described it well enough, otherwise feel free to text me back
I also do have skype: Snaske2 if that will make the communication easier
hope someone can help..
Best regards
29 Replies
Really hope someone could be so kind and help me out:D
Guess no one knows the answer:S
If you are struggling understanding what i wnat to achieve please just ask.
Hi Shamatix
I'm not 100% sure if i rght undestand your request, but my answer is so:
MomsGrundlag 2 = IF(CALCULATE(COUNTROWS('Indfortoldning');ALLEXCEPT('Indfortoldning';Indfortoldning[Løbenummer]))<2;AVERAGE(Indfortoldning[Moms grundlag]);BLANK())It's work like this, as you write (or I as understand :)
Best regs
McCow wrote:Hi Shamatix
I'm not 100% sure if i rght undestand your request, but my answer is so:
MomsGrundlag 2 = IF(CALCULATE(COUNTROWS('Indfortoldning');ALLEXCEPT('Indfortoldning';Indfortoldning[Løbenummer]))<2;AVERAGE(Indfortoldning[Moms grundlag]);BLANK())It's work like this, as you write (or I as understand :)
Best regs
Ill try it out, thanks a lot :)
I found a mistake you make ALL "Moms grundlag" blank if there is mulitple lines, I only want it blank if there is multiple lines and some of em has different momsgrundlag values so they are different from eachother, I hope that makes sense.
For example the above scenario can happen, and then I want it to be correct since ALL "Momsgrundlag" is the same number
Any way to get the total at the bottom as well?
A simplest way without complicate calculation is separate measure + Card visualisation. Totals in the table must be OFF.
