cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shamatix Post Patron
Post Patron

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.

 

http://ge.tt/3RvU3kn2

 

I have uploaded some sample data here and hopefully described it well enough, otherwise feel free to text me backSmiley Happy 
I also do have skype: Snaske2 if that will make the communication easierSmiley Happy

 

hope someone can help..


Best regards

 

29 REPLIES 29
Shamatix Post Patron
Post Patron

Re: Need help with a measure

Really hope someone could be so kind and help me out:D

Shamatix Post Patron
Post Patron

Re: Need help with a measure

Guess no one knows the answer:S
If you are struggling understanding what i wnat to achieve please just ask.

McCow Resolver III
Resolver III

Re: Need help with a measure

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 🙂

 

2017-12-17_12-11-21.png

 

Best regs

Shamatix Post Patron
Post Patron

Re: Need help with a measure


@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 🙂

 

2017-12-17_12-11-21.png

 

Best regs


Ill try it out, thanks a lot 🙂

Shamatix Post Patron
Post Patron

Re: Need help with a measure

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?

McCow Resolver III
Resolver III

Re: Need help with a measure

A simplest way without complicate calculation is separate measure + Card visualisation. Totals in the table must be OFF.

Shamatix Post Patron
Post Patron

Re: Need help with a measure


@McCow wrote:

A simplest way without complicate calculation is separate measure + Card visualisation. Totals in the table must be OFF.


What do you mean by the above, also I added a screenshot to my previous comment^^

Highlighted
McCow Resolver III
Resolver III

Re: Need help with a measure

@Shamatix all of your data has different values. whats must happen, if strings (records) with same seriennummer (Løbenummer) contains different VAT-values (Momsgrundlag)? Must be blank? And if same SN and same VAT-value must be printed (calculated as average value)? Correct?

Shamatix Post Patron
Post Patron

Re: Need help with a measure


@McCow wrote:

@Shamatix all of your data has different values. whats must happen, if strings (records) with same seriennummer (Løbenummer) contains different VAT-values (Momsgrundlag)? Must be blank? And if same SN and same VAT-value must be printed (calculated as average value)? Correct?


Exactly!:)

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors