Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
Joris_NL
Helper III
Helper III

Measure works for average %, but gives sum % for total

Hello all,

 

Still learning DAX. I've made this measure to calculate the percentage spent on a productnumber. Which works for looking at individual products. But in the (sub)totals, it sums up all the percentages (to values like 12118% etc). How can I make the totals as an average value?

 

 

 

Percentage spent = 
DIVIDE (
     MAX( 0;
        CALCULATE ( 
        SUM ( Table [Amount spent] ) ;
        ALL ( Table ) ;
        VALUES ( Table [xProductnumber] ) ) 
    ;
    [Measure for allowed amount per productnumber] 
    )

 

 

 

Results in:

 

Untitled.jpg

 

I tried adding 'AVERAGE (...) ' in the Percentage spent measure, but it's not allowed.

 

Thanks in advance!

Regards,

Joris

1 ACCEPTED SOLUTION

Thanks for the help!
I got it running without errors, but the problem still remains. The totals have even increased further to '133438%' etc. Other than that, the matrix shows the same outcomes as in my original post. Maybe you have another solution?
Please note I made two changes to make it running: European semicolon ';' instead of American ', ' and there's a closing bracket missing for MAX in my original post. So the new measure Im trying looks like this:

Percentage spent = 
VAR __M = 
	DIVIDE (
     MAX( 0;
        CALCULATE ( 
        SUM ( Table [Amount spent] ) ;
        ALL ( Table ) ;
        VALUES ( Table [xProductnumber] ) ) )
    ;
    [Measure for allowed amount per productnumber] 
    )
return 	
IF( 
		ISINSCOPE(  Table[xProductnumber] ) ;
		__M;
	
AVERAGEX(
	VALUES ( Table [xProductnumber] );
	DIVIDE (
     MAX( 0;
        CALCULATE ( 
        SUM ( Table [Amount spent] ) ;
        ALL ( Table ) ;
        VALUES ( Table [xProductnumber] ) ) )
    ;
    [Measure for allowed amount per productnumber] 
    )))

 

View solution in original post

5 REPLIES 5
Joris_NL
Helper III
Helper III

Solved it! Thanks for the ISINSCOPE advice, it's good to know that one. In the end the scope wasn't necessary, though would have worked as well. Your AVERAGEX was the sollution already. The final error I got was from the [measure] at the bottom which didn't work for totals. I just replaced that [measure] with a manual SUMX.

Thank you!

AVERAGEX(
    VALUES ( Table[xProductnumber] );
    DIVIDE (
     MAX( 0;
        CALCULATE (
        SUM ( Table[Amount spent] ) ;
        ALL ( Table) ;
        VALUES ( Table[xProductnumber] ) ) )
    ;
    SUMX ( DISTINCT ( Table[xProductnumber] )  ;( Table[Amount allowed] ) )
    ))
 
Fowmy
Super User
Super User

@Joris_NL 

Percentage spent = 
VAR __M = 
	DIVIDE (
     MAX( 0;
        CALCULATE ( 
        SUM ( Table [Amount spent] ) ;
        ALL ( Table ) ;
        VALUES ( Table [xProductnumber] ) ) 
    ;
    [Measure for allowed amount per productnumber] 
    )
return 	
IF( 
		ISINSCOPE(  Table[xProductnumber] ) ,
		__M,
	
AVERAGEX(
	VALUES ( Table [xProductnumber] ),
	DIVIDE (
     MAX( 0;
        CALCULATE ( 
        SUM ( Table [Amount spent] ) ;
        ALL ( Table ) ;
        VALUES ( Table [xProductnumber] ) ) 
    ;
    [Measure for allowed amount per productnumber] 
    ))))
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thanks for the help!
I got it running without errors, but the problem still remains. The totals have even increased further to '133438%' etc. Other than that, the matrix shows the same outcomes as in my original post. Maybe you have another solution?
Please note I made two changes to make it running: European semicolon ';' instead of American ', ' and there's a closing bracket missing for MAX in my original post. So the new measure Im trying looks like this:

Percentage spent = 
VAR __M = 
	DIVIDE (
     MAX( 0;
        CALCULATE ( 
        SUM ( Table [Amount spent] ) ;
        ALL ( Table ) ;
        VALUES ( Table [xProductnumber] ) ) )
    ;
    [Measure for allowed amount per productnumber] 
    )
return 	
IF( 
		ISINSCOPE(  Table[xProductnumber] ) ;
		__M;
	
AVERAGEX(
	VALUES ( Table [xProductnumber] );
	DIVIDE (
     MAX( 0;
        CALCULATE ( 
        SUM ( Table [Amount spent] ) ;
        ALL ( Table ) ;
        VALUES ( Table [xProductnumber] ) ) )
    ;
    [Measure for allowed amount per productnumber] 
    )))

 

@Joris_NL 

Could you share your PBI file, you may share with dummy/sample data

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Unfortunately sharing the file will take me hours. The data is privacy sensitive, the calculated tables and queries very extensive.

But I have an idea of whats going on. At the bottom of our dax, [Measure for allowed amount per productnumber] takes an AVERAGE value of the products, even for the totals. While we calculate the 'amount paid' as a SUMMED total. Meaning paid €2.1 million dividid by a budget of € 10K, gives a percentage of way too much. I think we should replace [Measure for allowed amount per productnumber] (at the bottom only) with something like 'the sum of per-product-average-amount-available'.


FYI, the data of Table looks like this:

 

xProductnr .Year .Month .Amount paid .Amount available .
1821120221110006373
1821120221210006373
182112023110006373
1821120232556373
182112023306373
184522023101482
184522023201482


The available amount is repeated, so we are showing it as an 'average'. In this example € 4.772 for the two products.
The amount paid is normal data, so we use 'sum' it. In this example € 3055 for two products.


The result we need is: (1000+1000+1000+55+0+0+0) / (6373 + 1482) = 39%

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.