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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
moeconsult
Helper V
Helper V

TotalCost based on Table or Matrix Value

I have pivot table in excel and I have calculated total cost based on the values of my calculations in the pivot table and I would do samething in excel but I am not sure how, Please below my sample data and formula used.

Course Duration*(Attendees feeding cost+Location Fees+Facility Fee)

 

NameCourse DurationNumber of AttendeesAttendees feeding costLocation FeesFacility FeeTotal Cost
CAS2.594515050612.5
 11515050205
FF121015050210
hs 1   0
POS2.56   0

 

Totalcost = =C5*(E5+F5+G5)

tt.JPG

@Ashish_Mathur @Greg_Deckler 

 

2 ACCEPTED SOLUTIONS

for me to be able to try the formula you supplied, i need to derive number of attendee: i used counifs please sample below 

=COUNTIFS(B:B,B2,C:C,C2,A:A,A2),please help with Dax for the countifs

DateNameStatusOutput
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
10/06/2019 08:30PATEnrolled6
10/06/2019 08:30PATEnrolled6
 PATEnrolled0
19/05/2019 23:00PATEnrolled1
10/06/2019 08:30PATEnrolled6
10/06/2019 08:30PATEnrolled6
10/06/2019 08:30PATEnrolled6
10/06/2019 08:30PATCancelled1
10/06/2019 08:30PATEnrolled6
10/07/2019 23:00CASEnrolled1
10/07/2019 23:00CAS - CLONED(25/07/2019)Enrolled2
10/07/2019 23:00CAS - CLONED(25/07/2019)Enrolled2

 

tt.JPG

@amitchandak 

 

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
amitchandak
Super User
Super User

Ideally, you should unpivot the table.

https://radacad.com/pivot-and-unpivot-with-power-bi

 

But if you have current format you can use a measure or column like

sumx(Table,Table[Course Duration]*(Table[Attendees feeding cost]+Table[Location Fees]+Table[Facility Fee]))

 

sumx(Table,Table[Course Duration]*(Table[Attendees feeding cost]+Table[Location Fees]+Table[Facility Fee])) is not returning the expected result, I think my explanation wasnt clear enough earlier 

 

Derived columns : 

FaVeFee = [Duration]*([Locationfee]+[FacilityFee])
FeedingCost = [Duration]*[Output]*[Feeding]
Total = [FaVeFee]+[FeedingCost]

 

I would like to archive below total with without having to create different colums in other to archive my result. I have attached a screenshot of the visulisation sample that I would like to archieve 

 

FacilityFeeFeedingDateLocationfeeDurationNameStatusOutputFaVeFeeFeedingCostTotal
50511/06/2019 07:001502.5CASEnrolled9500112.5612.5
50511/06/2019 07:001502.5CASEnrolled9500112.5612.5
50511/06/2019 07:001502.5CASEnrolled9500112.5612.5
50511/06/2019 07:001502.5CASEnrolled9500112.5612.5
50511/06/2019 07:001502.5CASEnrolled9500112.5612.5
50511/06/2019 07:001502.5CASEnrolled9500112.5612.5
50511/06/2019 07:001502.5CASEnrolled9500112.5612.5
50511/06/2019 07:001502.5CASEnrolled9500112.5612.5
50511/06/2019 07:001502.5CASEnrolled9500112.5612.5
  10/06/2019 08:30 2.5PATEnrolled6   
  10/06/2019 08:30 2.5PATEnrolled6   
     PATEnrolled0   
  19/05/2019 23:00  PATEnrolled1   
  10/06/2019 08:30 2.5PATEnrolled6   
  10/06/2019 08:30 2.5PATEnrolled6   
  10/06/2019 08:30 2.5PATEnrolled6   
  10/06/2019 08:30 2.5PATCancelled1   
  10/06/2019 08:30 2.5PATEnrolled6   
50510/07/2019 23:001501CASEnrolled220010210
50510/07/2019 23:001501CAEnrolled12005205
50510/07/2019 23:001501CASEnrolled220010210

 

Final Output: 

tt.JPG

@amitchandak 

Thanks

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  Thanks for your help with this, based on your Pbi file attached, is possible to show correct Row subtotal total . Thanks

 

Hi,

You may download the PBI file from here.

Hope this helps.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

hi @Ashish_Mathur , thanks for your, I am trying to use the formula you provide but I am getting attached error message below when I drag the total fee to visual canvaserror.JPG

 

Hi,

In the Query Editor, ensure that all numeric columns are formatted as Decimal numbers.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/


@Ashish_Mathur All numeric columns are formatted as Decimal numbers. but still getting same error message

 

Hi,

Share the link from where i can download your PBI file.  Ensure in that PBI file, my formulas as i had recommended are already written.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

All numeric columns are formatted as Decimal numbers. but still getting same error message

 

for me to be able to try the formula you supplied, i need to derive number of attendee: i used counifs please sample below 

=COUNTIFS(B:B,B2,C:C,C2,A:A,A2),please help with Dax for the countifs

DateNameStatusOutput
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
11/06/2019 07:00CASEnrolled9
10/06/2019 08:30PATEnrolled6
10/06/2019 08:30PATEnrolled6
 PATEnrolled0
19/05/2019 23:00PATEnrolled1
10/06/2019 08:30PATEnrolled6
10/06/2019 08:30PATEnrolled6
10/06/2019 08:30PATEnrolled6
10/06/2019 08:30PATCancelled1
10/06/2019 08:30PATEnrolled6
10/07/2019 23:00CASEnrolled1
10/07/2019 23:00CAS - CLONED(25/07/2019)Enrolled2
10/07/2019 23:00CAS - CLONED(25/07/2019)Enrolled2

 

tt.JPG

@amitchandak 

 

@moeconsult 

 

Try as a new column

 

Output = countx(filter(Table,Table[Name] =earlier(Table[Name]) && Table[Status] =earlier(Table[Status])),Table[Date])

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors