cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Hermes Regular Visitor
Regular Visitor

Wrong Grand Totals - SUMX ( VALUE ( ) ) ; does not help

Hi there,

 

I've come across situation I have trouble resolving myself. If you could take a look at my code.

 

Bussiness applications is like this; 

Our client has a quarter bonus paid based on sales value in quarter, increasing when certian treshold is achived. 

He will get 7% for every sales belowe 3000 / per quarter + 10% for sales above 3000. 

 

You can see my measure so far. It works fine on quarted level, but when it comes to grand total, sum is off.

 

Could you please point me to mistake I've made so I get proper sum for grand total?

 

 

retroProg.:=
VAR __WartZaOkr = 
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			ALLEXCEPT( Kalendarz ; Kalendarz[Kwartał roku] ) ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) 
	)

VAR __RetroProgi = 
	IF( __WartZaOkr <= 3000 ; 
		__WartZaOkr * 0,07 ; 
		( 3000 * 0,07 ) + ( ( __WartZaOkr - 3000 ) * 0,1 ) 
	) 

RETURN
__RetroProgi

 

 

1.PNG

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User II
Super User II

Re: Wrong Grand Totals - SUMX ( VALUE ( ) ) ; does not help

This should give you the correct total when you use it in the matrix together with the Quarter dimension. It's long and messy but it should work. If you want something more dynamic you will need to implement the weights (0,07 & 0,1) as calculated columns. But i cant really propose any solution there without seeing more of your datamodel. 

 

retroProg.:=
VAR __WartZaOkr = 
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			ALLEXCEPT( Kalendarz ; Kalendarz[Kwartał roku] ) ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) 
	)

VAR __RetroProgi = 
	IF( __WartZaOkr <= 3000 ; 
		__WartZaOkr * 0,07 ; 
		( 3000 * 0,07 ) + ( ( __WartZaOkr - 3000 ) * 0,1 ) 
	)

VAR Q1 =
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			Kalendarz ; Kalendarz[Kwartał roku] = "kwart. 1" ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) 
	)

VAR Q2 = 
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			Kalendarz ; Kalendarz[Kwartał roku] = "kwart. 2" ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) 
	)

VAR Q3 = 
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			Kalendarz ; Kalendarz[Kwartał roku] = "kwart. 3" ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) 
	)

VAR Q4 =  
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			Kalendarz ; Kalendarz[Kwartał roku] = "kwart. 4" ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) 
	)

VAR Total = 
	IF( Q1 <= 3000 ; 
		Q1 * 0,07 ; 
		( 3000 * 0,07 ) + ( ( Q1 - 3000 ) * 0,1 ) 
	) +

	IF( Q2 <= 3000 ; 
		Q2 * 0,07 ; 
		( 3000 * 0,07 ) + ( ( Q2 - 3000 ) * 0,1 ) 
	) +

	IF( Q3 <= 3000 ; 
		Q3 * 0,07 ; 
		( 3000 * 0,07 ) + ( ( Q3 - 3000 ) * 0,1 ) 
	) +

	IF( Q4 <= 3000 ; 
		Q4 * 0,07 ; 
		( 3000 * 0,07 ) + ( ( Q4 - 3000 ) * 0,1 ) 
	)

RETURN
IF( SELECTEDVALUE(Kalendarz[Kwartał roku]) = BLANK() ;
Total ;
__RetroProgi

 

View solution in original post

Super User II
Super User II

Re: Wrong Grand Totals - SUMX ( VALUE ( ) ) ; does not help

Try this and see if it works:

retroProg.:=
VAR __WartZaOkr = 
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		IF(
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			ALLEXCEPT( Kalendarz ; Kalendarz[Kwartał roku] ) ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) <= 3000 ;
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			ALLEXCEPT( Kalendarz ; Kalendarz[Kwartał roku] ) ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) * 0,07 ;
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			ALLEXCEPT( Kalendarz ; Kalendarz[Kwartał roku] ) ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) * 0,1
		)
	)
Return
__WartZaOkr


The reason the iteration isnt working is because we are iterating and summarizing first and then applying the multiplication after! I don't know if the code im providing works but it's worth a try! 

Br,
J

View solution in original post

6 REPLIES 6
Super User II
Super User II

Re: Wrong Grand Totals - SUMX ( VALUE ( ) ) ; does not help

Hello @Hermes,

When the application tries to calculate the "Total" it's not adding Q1 + Q2 +Q3 etc. Instead what it's actually doing is simply running the same calculation but without any filters on quarter. 

As an example, when the measure is calculated on the first row you can pretty much imagine that there is a "Q1" filter covering the entire measure. 

When the calculation for the total is occuring there is no quarter filter, therefore the total amount in the "__WartZaOkr" is way over 3000 and the entire amount will be multiplied with 0,1 instead of a combination of 0,07 & 0,1. 

In terms of how to solve this it kind of depends on how your report looks. Is retroProg. always displayed together with quarter?

Br,
J

Hermes Regular Visitor
Regular Visitor

Re: Wrong Grand Totals - SUMX ( VALUE ( ) ) ; does not help

Hello, 

 

There are many ways this raport could be displayed and I would like not to restrain myself. This is why I'v included ALL on clients and products tables in first VAR. In fact, this is only part of calculation, because in next step I calculate de facto bonus percentage no matter the filters (i.e. on product level) and calculate bonus from this filtered sales * obtained percentage. 

 

As I've said, it works greate on any level I've chcecked except for sums for more than one quarter.

Super User II
Super User II

Re: Wrong Grand Totals - SUMX ( VALUE ( ) ) ; does not help

This should give you the correct total when you use it in the matrix together with the Quarter dimension. It's long and messy but it should work. If you want something more dynamic you will need to implement the weights (0,07 & 0,1) as calculated columns. But i cant really propose any solution there without seeing more of your datamodel. 

 

retroProg.:=
VAR __WartZaOkr = 
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			ALLEXCEPT( Kalendarz ; Kalendarz[Kwartał roku] ) ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) 
	)

VAR __RetroProgi = 
	IF( __WartZaOkr <= 3000 ; 
		__WartZaOkr * 0,07 ; 
		( 3000 * 0,07 ) + ( ( __WartZaOkr - 3000 ) * 0,1 ) 
	)

VAR Q1 =
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			Kalendarz ; Kalendarz[Kwartał roku] = "kwart. 1" ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) 
	)

VAR Q2 = 
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			Kalendarz ; Kalendarz[Kwartał roku] = "kwart. 2" ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) 
	)

VAR Q3 = 
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			Kalendarz ; Kalendarz[Kwartał roku] = "kwart. 3" ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) 
	)

VAR Q4 =  
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			Kalendarz ; Kalendarz[Kwartał roku] = "kwart. 4" ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) 
	)

VAR Total = 
	IF( Q1 <= 3000 ; 
		Q1 * 0,07 ; 
		( 3000 * 0,07 ) + ( ( Q1 - 3000 ) * 0,1 ) 
	) +

	IF( Q2 <= 3000 ; 
		Q2 * 0,07 ; 
		( 3000 * 0,07 ) + ( ( Q2 - 3000 ) * 0,1 ) 
	) +

	IF( Q3 <= 3000 ; 
		Q3 * 0,07 ; 
		( 3000 * 0,07 ) + ( ( Q3 - 3000 ) * 0,1 ) 
	) +

	IF( Q4 <= 3000 ; 
		Q4 * 0,07 ; 
		( 3000 * 0,07 ) + ( ( Q4 - 3000 ) * 0,1 ) 
	)

RETURN
IF( SELECTEDVALUE(Kalendarz[Kwartał roku]) = BLANK() ;
Total ;
__RetroProgi

 

View solution in original post

Hermes Regular Visitor
Regular Visitor

Re: Wrong Grand Totals - SUMX ( VALUE ( ) ) ; does not help

Heh, I've market this solution as valid - and so it is 🙂 ... but I've realise that there are still wrong grand totals calculated whenever where is more than one year selected in filter. Each calculation for quarters become invalid, because in two years there are 8 quarters and for all of theme there should be separate calculation made - and right now there is one for each quarted combined times numeber of years displayed. Yet, there shoud be calculation for each year-quarter combination made separatly. But I have no idea how to go about it, and I still don't get why I can virtualy iterate over year-quarted with SUMX ( VALUES ( [year-quarter] ) )?

Super User II
Super User II

Re: Wrong Grand Totals - SUMX ( VALUE ( ) ) ; does not help

Try this and see if it works:

retroProg.:=
VAR __WartZaOkr = 
	SUMX( 
		VALUES( Kalendarz[Kwartał roku] ) ; 
		IF(
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			ALLEXCEPT( Kalendarz ; Kalendarz[Kwartał roku] ) ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) <= 3000 ;
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			ALLEXCEPT( Kalendarz ; Kalendarz[Kwartał roku] ) ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) * 0,07 ;
		CALCULATE( 
			SUMX( 
				FILTER( 'Sprzedaż │ Elementy' ; RELATED( 'Kontrahenci │ Wymiary'[Grupa] ) = "CLIENTX" ) ; 
				[Sprzedaż | Wartość] 
				) ; 
			ALLEXCEPT( Kalendarz ; Kalendarz[Kwartał roku] ) ; 
			ALL( 'Kontrahenci │ Wymiary' ) ; 
			ALL( 'Towary │ Wymiary' ) 
		) * 0,1
		)
	)
Return
__WartZaOkr


The reason the iteration isnt working is because we are iterating and summarizing first and then applying the multiplication after! I don't know if the code im providing works but it's worth a try! 

Br,
J

View solution in original post

Hermes Regular Visitor
Regular Visitor

Re: Wrong Grand Totals - SUMX ( VALUE ( ) ) ; does not help

This did the trick! Thank you very much. 🙂

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors