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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to show average at only last in matrix power bi, tried a lot but not getting right answer

Capture.PNG

HI 

I need to add only average at only last in matrix chart in power bi. 

like above image

 

Here i calculated average store 1 and 2 devided by 3 (as there are only 3 dates)

when i add average in Values sections in matrix it adds up every columns. I just want at the end of table

i searched google and search some  power bi forum but did not hel

2 ACCEPTED SOLUTIONS
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

If I understand correct, you could union two tables  in excel or in powerbi.

1,union two tables

Table = 
VAR _A=SUMMARIZE('PIT',[Date],[SP],[Total Pits Surveyed],"Cate","PIT")
VAR _B=SUMMARIZE('SL',[Date],[SP],[Sum of SLs Validated],"Cate","SL")
RETURN UNION(_A,_B)

2.create two measures.

PIT = 
VAR _a=CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="PIT"))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
SL = 
VAR _a=CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="SL"))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)

3.change label.

vyalanwumsft_0-1629100275628.png

The final output is shown below:

vyalanwumsft_1-1629100309542.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

1,union two tables

 

Table = VAR _A=SUMMARIZE('PIT',[SP],[Date],[Total Pits Surveyed],"Cate","PIT")
VAR _B=SUMMARIZE('SL',[SP],[Date],[Sum of SLs Validated],"Cate","SL")
VAR _C=SUMMARIZE('SL',[SP],"Date","Total","sum",BLANK(),"Cate","SL")
VAR _D=SUMMARIZE('PIT',[SP],"Date","Total","sum",BLANK(),"Cate","PIT")
RETURN UNION(_A,_B,_C,_D)

 

2.create two measures.

 

SL = 
VAR _a=IF(MAX('Table'[Date])="Total",CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL")),
CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="SL")))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
PIT = 
VAR _a=IF(MAX([Date])="Total",CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT")),
CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="PIT")))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)

 

3.change label.

vyalanwumsft_0-1629885637068.png

 

The final output is shown below:

vyalanwumsft_0-1630477065956.png

 

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

15 REPLIES 15
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

1,union two tables

 

Table = VAR _A=SUMMARIZE('PIT',[SP],[Date],[Total Pits Surveyed],"Cate","PIT")
VAR _B=SUMMARIZE('SL',[SP],[Date],[Sum of SLs Validated],"Cate","SL")
VAR _C=SUMMARIZE('SL',[SP],"Date","Total","sum",BLANK(),"Cate","SL")
VAR _D=SUMMARIZE('PIT',[SP],"Date","Total","sum",BLANK(),"Cate","PIT")
RETURN UNION(_A,_B,_C,_D)

 

2.create two measures.

 

SL = 
VAR _a=IF(MAX('Table'[Date])="Total",CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL")),
CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="SL")))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
PIT = 
VAR _a=IF(MAX([Date])="Total",CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT")),
CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="PIT")))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)

 

3.change label.

vyalanwumsft_0-1629885637068.png

 

The final output is shown below:

vyalanwumsft_0-1630477065956.png

 

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

If I understand correct, you could union two tables  in excel or in powerbi.

1,union two tables

Table = 
VAR _A=SUMMARIZE('PIT',[Date],[SP],[Total Pits Surveyed],"Cate","PIT")
VAR _B=SUMMARIZE('SL',[Date],[SP],[Sum of SLs Validated],"Cate","SL")
RETURN UNION(_A,_B)

2.create two measures.

PIT = 
VAR _a=CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="PIT"))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
SL = 
VAR _a=CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="SL"))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)

3.change label.

vyalanwumsft_0-1629100275628.png

The final output is shown below:

vyalanwumsft_1-1629100309542.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Heaps thanks. all good for now

v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

If you have slicer, please try it.

1.create two measures:

Store1 = 
VAR _a=CALCULATE(SUM([Value]),FILTER(ALLSELECTED('Table'),[Emp]=MAX([Emp])&&[Date]=MAX([Date])&&[cate]="Store1"))
VAR _b=CALCULATE(AVERAGE([Value]),FILTER(ALLSELECTED('Table'),[Emp]=MAX([Emp])&&[cate]="Store1"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
Store1 = 
VAR _a=CALCULATE(SUM([Value]),FILTER(ALLSELECTED('Table'),[Emp]=MAX([Emp])&&[Date]=MAX([Date])&&[cate]="Store1"))
VAR _b=CALCULATE(AVERAGE([Value]),FILTER(ALLSELECTED('Table'),[Emp]=MAX([Emp])&&[cate]="Store1"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)

The final output is shown below:

vyalanwumsft_0-1629095079937.pngvyalanwumsft_1-1629095095063.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi Thansk for your quick reply. can you please let me know what is the reason you add cate column in table. i do have data like this 

vcm2778_0-1629095490178.png

 

Hi, @Anonymous

vyalanwumsft_0-1629096862272.png

I am making simple data based on the pictures you provided, it is naturally better if you can share your files then as well as the results you want to output. so can you share me a .pbix or Complete data and the results you want to output?


 

Anonymous
Not applicable

if you can access this link then let me know. i have placed excel file to below location

https://www.dropbox.com/scl/fi/xdkziuczp8d83r7m9c7p8/Book2.xlsx?dl=0&rlkey=dbxq8yl9b072gbcqy4j7yizhk

 

in both tab (Pit and SL) names are same so i had grouped by name in power bi so i do have 4 columns (1) Name (2) Date (3) Pit survey total (4) SL Validated total

 

And the output like same image above but Store1= Pit and Store2=SL

Anonymous
Not applicable

HI, can you please let me know if you get my file from dropbox and please let me know any outcomes

Thanks

 

Hi, @Anonymous ;

You can check my last reply record, does it meet your needs?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

HI, can you please add Total too.

Thanks

Anonymous
Not applicable

i really appriciate your  support big thanks, how can i send my files 

Hi, @Anonymous ;

You can also use screenshots.

the most important parts are:
1. Sample data as text or screenshots.
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

You could create a measure .

Method1:

Measure = 
var _store1=CALCULATE(AVERAGE([Value]),FILTER(ALLEXCEPT('Table','Table'[Emp]),[cate]="Store1"))
var _store2=CALCULATE(AVERAGE([Value]),FILTER(ALLEXCEPT('Table','Table'[Emp]),[cate]="Store2"))
var _max=CALCULATE(MAX([Date]),ALL('Table'))
return IF(HASONEVALUE('Table'[cate]),SUM([Value]),IF(HASONEVALUE('Table'[Date])&&MAX([Date])=_max,_store1,IF(MAX([Date])<>_max,BLANK(),_store2)))

Then change label 

vyalanwumsft_1-1628751853614.png

The final output is shown below:

vyalanwumsft_0-1628751355696.png

Method2:

1.add rows in power query 

vyalanwumsft_2-1628752059542.png

2.create a measure.

VAR _store1 =
    CALCULATE (
        AVERAGE ( [Value] ),
        FILTER ( ALLEXCEPT ( 'Table (2)', 'Table (2)'[Emp] ), [cate] = "Store1" )
    )
VAR _store2 =
    CALCULATE (
        AVERAGE ( [Value] ),
        FILTER ( ALLEXCEPT ( 'Table (2)', 'Table (2)'[Emp] ), [cate] = "Store2" )
    )
RETURN
    IF (
        MAX ( [cate] ) = "Store1"&& MAX ( [Date] ) = "Average",
        _store1,IF (MAX ( [cate] ) = "Store2"&& MAX ( [Date] ) = "Average",_store2,SUM ( [Value] ) ))

The final output is shown below:

vyalanwumsft_3-1628752399889.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

its a amazing help. i will check and update you. really appriciate your  support  big thanks

Anonymous
Not applicable

if i need to add date slicer then how this measure will be changed automatically as per slicer dates range

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors