cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vcm2778
Helper II
Helper II

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, @vcm2778 ;

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, @vcm2778 ;

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, @vcm2778 ;

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

v-yalanwu-msft
Community Support
Community Support

Hi, @vcm2778 ;

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

Heaps thanks. all good for now

v-yalanwu-msft
Community Support
Community Support

Hi, @vcm2778 ;

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.

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, @vcm2778

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?


 

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

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

Thanks

 

Hi, @vcm2778 ;

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.

HI, can you please add Total too.

Thanks

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

Hi, @vcm2778 ;

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, @vcm2778 ;

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.

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

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors