Reply
Member
Posts: 135
Registered: ‎05-11-2017

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

 

Member
Posts: 135
Registered: ‎05-11-2017

Re: Need help with a measure

Really hope someone could be so kind and help me outSmiley Very Happy

Member
Posts: 135
Registered: ‎05-11-2017

Re: Need help with a measure

Guess no one knows the answerSmiley Frustrated
If you are struggling understanding what i wnat to achieve please just ask.

Member
Posts: 88
Registered: ‎12-08-2017

Re: Need help with a measure

[ Edited ]

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 Smiley Happy

 

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

 

Best regs

Member
Posts: 135
Registered: ‎05-11-2017

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 Smiley Happy

 

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

 

Best regs


Ill try it out, thanks a lot Smiley Happy

Member
Posts: 135
Registered: ‎05-11-2017

Re: Need help with a measure

[ Edited ]

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?

Member
Posts: 88
Registered: ‎12-08-2017

Re: Need help with a measure

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

Member
Posts: 135
Registered: ‎05-11-2017

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
Member
Posts: 88
Registered: ‎12-08-2017

Re: Need help with a measure

[ Edited ]

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

Member
Posts: 135
Registered: ‎05-11-2017

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!Smiley Happy