Forum Discussion

Shamatix's avatar
Shamatix
Icon for Post Partisan rankPost Partisan
8 years ago

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,Ind...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Shamatix's avatar
Shamatix
Icon for Post Partisan rankPost Partisan
8 years ago
I'll try get some data
McCow's avatar
McCow
Icon for Resolver III rankResolver III
8 years ago

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's avatar
    Shamatix
    Icon for Post Partisan rankPost Partisan
    8 years ago

    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's avatar
    McCow
    Icon for Resolver III rankResolver III
    8 years ago

    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's avatar
    McCow
    Icon for Resolver III rankResolver III
    8 years ago

    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: