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
cosminc
Post Partisan
Post Partisan

new table summarised in a pivot but with sum

Hi,

i struggle with a new table and i don't know how to put the right syntax

filteredandpivotedsource =
VAR MaxYear =
CALCULATE ( MAX ( Source[Year] ), ALL ( Source ) )
RETURN
SUMMARIZE (
FILTER ( Source, Source[Year] = MaxYear ),
Source[Salesmen],
Source[Sales],
"Year", MaxYear
)
 
so i filtered the main source with max year but also i want to have it with unique Salesmen and sum Sales for each
i found a solution something like this
New Table = SUMMARIZECOLUMNS('Source'[Salesmen])
 
can you help me please with the right syntax
thanks,
Cosmin
1 ACCEPTED SOLUTION

@cosminc  Please create a New Table as below

 

Test293Out = 
VAR _MaxYear = MAX(Test293Summarize[Year])
RETURN SUMMARIZE(FILTER(Test293Summarize,Test293Summarize[Year]=_MaxYear),Test293Summarize[Salesman],"Sales",SUM(Test293Summarize[Sales]))

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

4 REPLIES 4
PattemManohar
Community Champion
Community Champion

@cosminc  Please post sample test data and expected output to provide you an accurate solution.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




main base

 

YearMonthNoSalesmanSales
20192George59
20191George68
20181George71
20182George94
20183George89
20184George83
20185George53
20186George91
20187George83
20188George51
20189George78
201810George64
201811George88
201812George99
20171George97
20172George93
20173George100
20174George82
20175George92
20176George65
20177George90
20178George52
20179George99
201710George80
201711George67
201712George95
20191Alex59
20181Alex86
20182Alex50
20183Alex65
20184Alex79
20185Alex63
20186Alex94
20187Alex97
20188Alex96
20189Alex98
201810Alex97
201811Alex80
201812Alex92
20171Alex87
20172Alex79
20173Alex57
20174Alex61
20175Alex88
20176Alex77
20177Alex62
20178Alex78
20179Alex60
201710Alex55
201711Alex63
201712Alex57
20182Teo88
20183Teo56
20184Teo85
20185Teo80
20186Teo86
20187Teo73
20188Teo51
20189Teo67
20192Jenna93
20191Jenna63
20181Jenna100

 

 

expected results:

only 2019 and each salesmen only once 

 

Salesmen  Sales

George      ...

Alex           ...

 Teo           ...

Jenna         ...

if has a rank also after sales is a plus, but even without it is fine

further i work with ranks and main base is to bigger; the expression work very hard

so i improvise with a small table

Thanks,

Cosmin

@cosminc  Please create a New Table as below

 

Test293Out = 
VAR _MaxYear = MAX(Test293Summarize[Year])
RETURN SUMMARIZE(FILTER(Test293Summarize,Test293Summarize[Year]=_MaxYear),Test293Summarize[Salesman],"Sales",SUM(Test293Summarize[Sales]))

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Many thanks!

Cosmin

 

 

 

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.