Trouble with a measure

[Shamatix]

Hello fellow users of Power Bi,

Well ill try to describe one of my issues:

I am making a table with a ton of invoice data and of course some of the invoices has multiple lineitems which can be seen on the right table of the two below.

I have done some tests but I cant seem to be able to achieve the result I want to.

If you take a look at the left table the Column Test2 and Test3 are actually bring me the correct "Moms grundlag" as its giving me the average of the invoice which is what I want, but the "Total" at the bottom still gives me the total of all the lines, it aint giving me the sum of all the averages pr invoice which is what I want. In the left table I want the total to be 68,902,632, not 175.407.896.

Below you will be able to see the measures I have been trying to fiddle around with:

Test1 = If(HASONEVALUE(Indfortoldning[Løbenummer]),CALCULATE(SUM(Indfortoldning[Moms grundlag])),sumx(Indfortoldning,CALCULATE(AVERAGE(Indfortoldning[Moms grundlag]))))          - THIS ONE IS COMPLETELY INCORRECT

Test2 = If(HASONEVALUE(Indfortoldning[Løbenummer]),CALCULATE(AVERAGE(Indfortoldning[Moms grundlag])),sumx(Indfortoldning,CALCULATE(AVERAGE(Indfortoldning[Moms grundlag]))))        - This one seems to give almost the correct result, but the total at the bottom is still incorrect

Test3 = If(HASONEVALUE(Indfortoldning[Løbenummer]),CALCULATE(AVERAGE(Indfortoldning[Moms grundlag])),sumx(Indfortoldning,CALCULATE(SUM(Indfortoldning[Moms grundlag]))))                - This one seems to give almost the correct result, but the total at the bottom is still incorrect

Also I would love if someone would be abl to have a skype call with some other issues as I am trying to convert a huge qlikview report into a Power Bi report, but I am having some struggles sadly.

Best regards

[Ashish_Mathur]

Hi,

Try this

=If(HASONEVALUE(Indfortoldning[Løbenummer]),AVERAGE(Indfortoldning[Moms grundlag]),SUMX(SUMMARIZE(Indfortoldning,Indfortoldning[Løbenummer],"ABCD",AVERAGE(Indfortoldning[Moms grundlag])),[ABCD]))

Hope this helps.

[Shamatix]

---

@Ashish_Mathur wrote:

Hi,

Try this

=If(HASONEVALUE(Indfortoldning[Løbenummer]),AVERAGE(Indfortoldning[Moms grundlag]),SUMX(SUMMARIZE(Indfortoldning,Indfortoldning[Løbenummer],"ABCD",AVERAGE(Indfortoldning[Moms grundlag])),[ABCD]))

Hope this helps.

---

Hey there,

Thanks a ton for the above measure, was just what I wanted.

Now I have however stumpled into another issue I hadnt seen coming so I need this measure changed up a bit to work the same way qlikview does. I will do my best to explain the issue but please ask if there is any questions:

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 hope the above made sense.

[Ashish_Mathur]

Hi,

Share a sample dataset and show the expected result there.

[Shamatix]

---

@Ashish_Mathur wrote:

Hi,

Share a sample dataset and show the expected result there.

---

Hey Ashis,

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

[Ashish_Mathur]

Hi,

I am still not clear with your requirement but try this measure

=If(HASONEVALUE(Indfortoldning[Løbenummer]),if(DISTINCTCOUNT(Indfortoldning[Moms grundlag])>1,BLANK(),AVERAGE(Indfortoldning[Moms grundlag])),SUMX(SUMMARIZE(Indfortoldning,Indfortoldning[Løbenummer],"ABCD",if(DISTINCTCOUNT(Indfortoldning[Moms grundlag])>1,BLANK(),AVERAGE(Indfortoldning[Moms grundlag]))),[ABCD]))

Hope this helps.

[Shamatix]

---

@Ashish_Mathur wrote:

Hi,

I am still not clear with your requirement but try this measure

=If(HASONEVALUE(Indfortoldning[Løbenummer]),if(DISTINCTCOUNT(Indfortoldning[Moms grundlag])>1,BLANK(),AVERAGE(Indfortoldning[Moms grundlag])),SUMX(SUMMARIZE(Indfortoldning,Indfortoldning[Løbenummer],"ABCD",if(DISTINCTCOUNT(Indfortoldning[Moms grundlag])>1,BLANK(),AVERAGE(Indfortoldning[Moms grundlag]))),[ABCD]))

Hope this helps.

---

https://community.powerbi.com/t5/Desktop/Need-help-with-a-measure/m-p/326065#M145459 

I made a new thread, however I am really struggling converting my Qlikview to a 1:1 Power Bi, would love to get you on skype and be able to show you^^

[Ashish_Mathur]

Hi,

Does my suggested formula work?

[stretcharm]

I create a small test data set and these measures worked for me.

AvgMeasure = CALCULATE(AVERAGE(InvoiceLines[Amount]))

SumAvg = sumx(Invoices, [AvgMeasure])

The worked if I have one or more Invoice Items.

I have an invoice table and an Invoice Line Table joined on Invoice ID.

[Shamatix]

Really no one knows the answer :S?

Or simply do not understand my question?

[Shamatix]

Hey fellow users of Power Bi,

I am in the making of a Power Bi report, actually trying to convert a rather complicated Qlikview report to a Power Bi report, but I have quite some struggles, a few more than I can simply try to explain here, therefor I am seeking a friendly person who has knowledge with Power Bi along with Qlikview and is up for a lil chat where I can try to explain some of my struggles and perhaps bounce a few ideas with.

Best regards Henrik V

Skype: Snaske2

[Shamatix]

Guess no one had the time sadly.

Well ill try to describe one of my issues:

I am making a table with a ton of invoice data and of course some of the invoices has multiple lineitems which can be seen on the right table of the two below.

I have done some tests but I cant seem to be able to achieve the result I want to.

If you take a look at the left table the Column Test2 and Test3 are actually bring me the correct "Moms grundlag" as its giving me the average of the invoice which is what I want, but the "Total" at the bottom still gives me the total of all the lines, it aint giving me the sum of all the averages pr invoice which is what I want. In the left table I want the total to be 68,902,632, not 175.407.896.

Below you will be able to see the measures I have been trying to fiddle around with:

Test1 = If(HASONEVALUE(Indfortoldning[Løbenummer]),CALCULATE(SUM(Indfortoldning[Moms grundlag])),sumx(Indfortoldning,CALCULATE(AVERAGE(Indfortoldning[Moms grundlag]))))          - THIS ONE IS COMPLETELY INCORRECT

Test2 = If(HASONEVALUE(Indfortoldning[Løbenummer]),CALCULATE(AVERAGE(Indfortoldning[Moms grundlag])),sumx(Indfortoldning,CALCULATE(AVERAGE(Indfortoldning[Moms grundlag]))))        - This one seems to give almost the correct result, but the total at the bottom is still incorrect

Test3 = If(HASONEVALUE(Indfortoldning[Løbenummer]),CALCULATE(AVERAGE(Indfortoldning[Moms grundlag])),sumx(Indfortoldning,CALCULATE(SUM(Indfortoldning[Moms grundlag]))))                - This one seems to give almost the correct result, but the total at the bottom is still incorrect

I hope you understand my issue and are able to help, otherwise feel free to ask 🙂 

[Shamatix]

Really no one knows the answer :S?

Or simply do not understand my question?

[stretcharm]

I create a small test data set and these measures worked for me.

AvgMeasure = CALCULATE(AVERAGE(InvoiceLines[Amount]))

SumAvg = sumx(Invoices, [AvgMeasure])

The worked if I have one or more Invoice Items.

I have an invoice table and an Invoice Line Table joined on Invoice ID.