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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Giant91
Frequent Visitor

Grand total

Hi all, I have a “problem”, I want show in the pivot the Total average for the sellout, is possibile create a formula with dax?

I just want  the results that I have if I have the grand total for rows, but I want it only for sellout and filtered week 14-23.

thank you for the help.

 

pivot.PNG

15 REPLIES 15
Greg_Deckler
Super User
Super User

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Anyone can help me?

Yes, would be happy to help but there is not enough information presented. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I’m trying to the total average across the filtered weeks.

the measure for the stock is : Stock:=CALCULATE(SUM([Stock]);tabella[week stock]=”27″)

and for the sellout : sellout:=CALCULATE(AVERAGE([Sellout]);FILTER(table;table[Week]<“w24″))

 

I want show the average of filtered weeks for the sellout in a single column on the right (like grand total), is it possible?

I still do no know what you want and I am not going to hand type a bunch of columns and numbers trying to fumble around to find the answer. 

 

Post sample data in a format that I can copy and paste (not a picture, ideally text in a table). Then, provide the expected results for the data you presented. This allows me and other forum members to copy the data into Power BI and create the necessary formulas that result in what you expect to see. This removes ambiguity and is far less frustrating than hand typing a bunch of data and then guessing what exactly it is that you want to see.

 

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

 so suppose this is my database :

 

YearWeekSoldToNameSellout
2018W23Customer X3
2018W23Customer X4
2018w24Customer X5
2018W25Customer X6
2018W26Customer X8
2018w27Customer X10

 

i want show this in a pivot table:

 

SoldToNameAverage sellout w23Average sellout w24Averagesellout  w25Averages sellout of filtered week (Total Average)
Customer x3,5564,83

 

the problem is that i can't create the last column (total average), thank for your help

OK, this is much clearer. Thank-you. So, then I have this last question, how are you doing the "Average sellout of w23"? Is that a measure or are you putting the weeks as columns?

 

If you have your matrix configured such that you have "SoldToName" in your Rows, "Week" in your Columns and "Average of Sellout" in your Values, then your measure would be:

 

Measure = 
VAR __tmpTable = SUMMARIZE(ALLSELECTED(Table),[SoldToName],[Week],"__average",AVERAGE([Sellout]))
RETURN
AVERAGEX(__tmpTable,[__average])

This creates a temp table with the selected items from your table summarized by customer and week. Then you just have to take the average of those entries to get the value you are looking for.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

first of all, tankk you very much for your help,

 

my matrix is like this:

 

YearWeekSoldToNameSellout
2018W23Customer X3
2018W23Customer X4
2018w24Customer X5
2018W25Customer X6
2018W26Customer X8
2018w27Customer X10

 

and i i try your formula:

 

=SUMMARIZE(ALLSELECTED(table);[SoldToName];table[Week];"Total average";AVERAGE([MeasureSellout]))

 

i have this error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. 

 

what im doing wring?

This is the data that I am using in a table called "Sellout"

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFDSUQo3MgaSzqXFJfm5qUUKEUCOsVKsDj55E1R5E3R5U1R5U3R5M1R5M3R5C1R5c3R5QwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Week = _t, SoldToName = _t, Sellout = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Week", type text}, {"SoldToName", type text}, {"Sellout", Int64.Type}})
in
    #"Changed Type"

Only thing that I can see in your formula is that I do not know what [MeasureSellout] is? Oh, and if you are only using that function that is going to return a table, that is why I setup a VAR and then did an AVERAGEX, that gets you to one value.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

[MeasureSellout] is just sellout in your data, the formula:VAR __tmpTable = SUMMARIZE(ALLSELECTED(Table),[SoldToName],[Week],"__average",AVERAGE([Sellout])) return me this error The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value

I don't see a RETURN function in there. If you use VAR, you must use RETURN.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Now im using this formula:

 

Average:=VAR _sellout = SUMMARIZE(ALLSELECTED(tabella);[SoldToName];[Week];"average";AVERAGE([Sellout]))

RETURN

AVERAGEX(_sellout;[average])

 

but the result is the same as before :

 

SoldToName    W14       W15          W16           W17            W18        W19          W20                 w21           w22                  w23

x111111111
y111111111
z111111111

 

i dont have a column that give to me the sum for the week, maybe beacause i put week on columns in the field of pivot table?

I'm not sure where else to go from here but I have attached the PBIX file. Seems to work for me with the parameters you have given. Hopefully you can figure it out from there.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

it seems works on Power BI, maybe the problem is that i use power pivot on excel and than i create a pivto table?

Perhaps although I would expect that if your pivot table was configured like the matrix visualization in Power BI then it should work the same. I don't do Excel though.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.