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

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 Member
Member

Re: Need help with a measure

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

Shamatix Member
Member

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.

McCow Member
Member

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

 

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

 

Best regs

Shamatix Member
Member

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

Shamatix Member
Member

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 Member
Member

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 Member
Member

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^^

McCow Member
Member

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 Member
Member

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 396 members 4,445 guests
Please welcome our newest community members: