cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
McCow Member
Member

Re: Need help with a measure


@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

PBIDesktop_2017-12-18_10-31-00.png


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

Shamatix Member
Member

Re: Need help with a measure


@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

PBIDesktop_2017-12-18_10-31-00.png


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 Member
Member

Re: Need help with a measure

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 Member
Member

Re: Need help with a measure

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 

L0is0dd

McCow Member
Member

Re: Need help with a measure

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 Member
Member

Re: Need help with a measure

I'll try get some data
McCow Member
Member

Re: Need help with a measure

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 Member
Member

Re: Need help with a measure

Hey,

 

It's still wrongoAIfJiY

 

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 Member
Member

Re: Need help with a measure

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

Highlighted
McCow Member
Member

Re: Need help with a measure

@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:
2017-12-19.png