Need help with a measure

[Shamatix]

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 back 
I also do have skype: Snaske2 if that will make the communication easier

hope someone can help..

Best regards

[McCow]

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

[Shamatix]

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]

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

[Shamatix]

---

@McCow wrote:

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

---

I tried to use the Card visualization with yoru measure but it turns out blank

[McCow]

---

@Shamatix wrote:

---

@McCow wrote:

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

---

I tried to use the Card visualization with yoru measure but it turns out blank

---

No, no, as Card visualization must be used ANOTHER measure (ex. MomsGrundlag) not MomsGrundlag2.  Or Total of column.

[Shamatix]

---

@McCow wrote:

---

@Shamatix wrote:

---

@McCow wrote:

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

---

I tried to use the Card visualization with yoru measure but it turns out blank

---

No, no, as Card visualization must be used ANOTHER measure (ex. MomsGrundlag) not MomsGrundlag2.  Or Total of column.

---

But the normal "MomsGrundlag" doesnt show the correct result? as it still calculates the fields that is supposed to be blank but arent?

[McCow]

Hi @Shamatix

please, check THIS solution.

I added two calculated columns. It is old school, but i suggest, that it is right solution.

You must use "New_Moms_grundlag" column. And "Rest" is another technical column (for test), must be leaved, The Nr_Qty and VarX - not obligated and can be erased.

[Shamatix]

This looks promising, however I found quite a lot of mistakes =/ 

As you can see on the below picture this is one of many where it does not take the average 

[Shamatix]

I'll try get some data

[McCow]

I modyfied Rest and New_Moms_grundlag columns from last example PBIX ^^^.

Pls try this:

Rest = 
        ISO.CEILING(CALCULATE(SUM(Indfortoldning[Moms grundlag]);ALLEXCEPT(Indfortoldning;Indfortoldning[Løbenummer])) -
        [Moms grundlag] * CALCULATE(COUNTROWS('Indfortoldning');ALLEXCEPT('Indfortoldning';Indfortoldning[Løbenummer]));0,1)

and

New_Moms_grundlag = 
IF(
            ABS([Rest])  < 1;
            ([Moms grundlag]);
            BLANK()
)// if

[Shamatix]

Hey,

It's still wrong

I have uploaded most of my data set http://ge.tt/5wv6omn2 just dont apply the changes as I have removed my connection string

Also I know from Qlikview the "Moms Grundlag" should be around 

[McCow]

Hi @Shamatix,

my formula above is in principal correct. You must use Average or Don't summarize option for "New_Moms_grundlag", not Sum.

And the sum of all values (calculated in Excel) in this case is very near to QlikView: 2 864 629 678,17 .

I make the summarize measure (or change the formula for this column) a bit later.

Best regs

[McCow]

@Shamatix
With this code the column "New_Moms_grundlag" can be summarized:

New_Moms_grundlag = 
IF(
            ABS([Rest])  < 1 ;
            CALCULATE(AVERAGEX(Indfortoldning;[Moms grundlag]);ALLEXCEPT(Indfortoldning;Indfortoldning[Løbenummer])) / Indfortoldning[Nr_Qty];
            BLANK()
)// if

And the construction of "Indfortoldning[Nr_Qty]" (Løbenummer-Quantity column) above can be changed with:

Nr_Qty = 
	CALCULATE(COUNTROWS('Indfortoldning');ALLEXCEPT('Indfortoldning';Indfortoldning[Løbenummer])) 

 Here is the sample:

[McCow]

I look for better workaround solution and in this case i need all ther rows in your example set. It is possible to add?  I feel we are closer and closer

[Shamatix]

Hey McCow,

I have uploaded some sample data on the link below so you can see it still makes certain invoices blank, eventho the "Moms Grundlag" is the same amount for each line, so it should just show the avarage in this case.

http://ge.tt/4HRrAmn2

[Shamatix]

---

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

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

---

@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!:)

[McCow]

@Shamatix,

try this and let me know pls if it work correct or no on yours data:

MomsGrundlag 2 = IF(CALCULATE(COUNTROWS('Indfortoldning');ALLEXCEPT('Indfortoldning';Indfortoldning[Løbenummer]))<2 || 
	AVERAGEX('Indfortoldning';Indfortoldning[Moms grundlag]) <> MEDIANX('Indfortoldning';Indfortoldning[Moms grundlag]); 
	AVERAGE(Indfortoldning[Moms grundlag]);
	BLANK()
)//if

[Shamatix]

---

@McCow wrote:

@Shamatix,

try this and let me know pls if it work correct or no on yours data:

MomsGrundlag 2 = IF(CALCULATE(COUNTROWS('Indfortoldning');ALLEXCEPT('Indfortoldning';Indfortoldning[Løbenummer]))<2 || 
	AVERAGEX('Indfortoldning';Indfortoldning[Moms grundlag]) <> MEDIANX('Indfortoldning';Indfortoldning[Moms grundlag]); 
	AVERAGE(Indfortoldning[Moms grundlag]);
	BLANK()
)//if

---

Thanks a ton for the help, almost there:

https://i.imgur.com/FVnYWXK.png 

As you see on the above picture, it makes this one blank, eventho the momsgrundlag (vatamount)  is the same