cancel
Showing results for
Did you mean:  Helper II

## How to show average at only last in matrix power bi, tried a lot but not getting right answer 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  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. The final output is shown below: 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.  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. The final output is shown below: 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.

15 REPLIES 15  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. The final output is shown below: 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.  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. The final output is shown below: 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.  Helper II

Heaps thanks. all good for now  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:  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.  Helper II

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   Community Support

Hi, @vcm2778 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？  Helper II

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  Helper II

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

Thanks  Community Support

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.  Helper II

Thanks  Helper II

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

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.  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 The final output is shown below: Method2: 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: 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.  Helper II

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

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