Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
OK, @Shamatix, you right, possible my mistake.
pls check this formula (based on previous):
MomsGrundlag 2 = IF(CALCULATE(COUNTROWS('Indfortoldning');ALLEXCEPT('Indfortoldning';Indfortoldning[Løbenummer]))<2 || // AVERAGEX('Indfortoldning';Indfortoldning[Moms grundlag]) <> MEDIANX('Indfortoldning';Indfortoldning[Moms grundlag]); VARX.P('Indfortoldning';Indfortoldning[Moms grundlag]) = 0; AVERAGE(Indfortoldning[Moms grundlag]); BLANK() )//if
MfG
@McCow wrote:OK, @Shamatix, you right, possible my mistake.
pls check this formula (based on previous):
MomsGrundlag 2 = IF(CALCULATE(COUNTROWS('Indfortoldning');ALLEXCEPT('Indfortoldning';Indfortoldning[Løbenummer]))<2 || // AVERAGEX('Indfortoldning';Indfortoldning[Moms grundlag]) <> MEDIANX('Indfortoldning';Indfortoldning[Moms grundlag]); VARX.P('Indfortoldning';Indfortoldning[Moms grundlag]) = 0; AVERAGE(Indfortoldning[Moms grundlag]); BLANK() )//ifMfG
I really cant seem to figure out why its working correctly on some and not on some of the others.
@McCow wrote:OK, @Shamatix, you right, possible my mistake.
pls check this formula (based on previous):
MomsGrundlag 2 = IF(CALCULATE(COUNTROWS('Indfortoldning');ALLEXCEPT('Indfortoldning';Indfortoldning[Løbenummer]))<2 || // AVERAGEX('Indfortoldning';Indfortoldning[Moms grundlag]) <> MEDIANX('Indfortoldning';Indfortoldning[Moms grundlag]); VARX.P('Indfortoldning';Indfortoldning[Moms grundlag]) = 0; AVERAGE(Indfortoldning[Moms grundlag]); BLANK() )//ifMfG
@McCow wrote:OK, @Shamatix, you right, possible my mistake.
pls check this formula (based on previous):
MomsGrundlag 2 = IF(CALCULATE(COUNTROWS('Indfortoldning');ALLEXCEPT('Indfortoldning';Indfortoldning[Løbenummer]))<2 || // AVERAGEX('Indfortoldning';Indfortoldning[Moms grundlag]) <> MEDIANX('Indfortoldning';Indfortoldning[Moms grundlag]); VARX.P('Indfortoldning';Indfortoldning[Moms grundlag]) = 0; AVERAGE(Indfortoldning[Moms grundlag]); BLANK() )//ifMfG
Getting closer and closer:D
However this one turns up blank as well however here https://i.imgur.com/0XiOemi.png
it turns up blank as well (5 rows) same løbenummer and vatamount (moms grundlag)
@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 🙂
Best regs
Ill try it out, thanks a lot 🙂
Really hope someone could be so kind and help me out:D
Guess no one knows the answer:S
If you are struggling understanding what i wnat to achieve please just ask.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |