Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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.
@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.
Hi,
Share a sample dataset and show the expected result there.
@Ashish_Mathur wrote:Hi,
Share a sample dataset and show the expected result there.
Hey Ashis,
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:)
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.
@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^^
Hi,
Does my suggested formula work?
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.
Really no one knows the answer :S?
Or simply do not understand my question?
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
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 🙂
Really no one knows the answer :S?
Or simply do not understand my question?
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.
User | Count |
---|---|
88 | |
88 | |
73 | |
66 | |
57 |
User | Count |
---|---|
136 | |
108 | |
91 | |
83 | |
69 |