Forum Discussion
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)
| Name | Course Duration | Number of Attendees | Attendees feeding cost | Location Fees | Facility Fee | Total Cost |
| CAS | 2.5 | 9 | 45 | 150 | 50 | 612.5 |
| 1 | 1 | 5 | 150 | 50 | 205 | |
| FF | 1 | 2 | 10 | 150 | 50 | 210 |
| hs | 1 | 0 | ||||
| POS | 2.5 | 6 | 0 |
Totalcost = =C5*(E5+F5+G5)
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
Date Name Status Output 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 10/06/2019 08:30 PAT Enrolled 6 10/06/2019 08:30 PAT Enrolled 6 PAT Enrolled 0 19/05/2019 23:00 PAT Enrolled 1 10/06/2019 08:30 PAT Enrolled 6 10/06/2019 08:30 PAT Enrolled 6 10/06/2019 08:30 PAT Enrolled 6 10/06/2019 08:30 PAT Cancelled 1 10/06/2019 08:30 PAT Enrolled 6 10/07/2019 23:00 CAS Enrolled 1 10/07/2019 23:00 CAS - CLONED(25/07/2019) Enrolled 2 10/07/2019 23:00 CAS - CLONED(25/07/2019) Enrolled 2
13 Replies
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]))
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
Date Name Status Output 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 11/06/2019 07:00 CAS Enrolled 9 10/06/2019 08:30 PAT Enrolled 6 10/06/2019 08:30 PAT Enrolled 6 PAT Enrolled 0 19/05/2019 23:00 PAT Enrolled 1 10/06/2019 08:30 PAT Enrolled 6 10/06/2019 08:30 PAT Enrolled 6 10/06/2019 08:30 PAT Enrolled 6 10/06/2019 08:30 PAT Cancelled 1 10/06/2019 08:30 PAT Enrolled 6 10/07/2019 23:00 CAS Enrolled 1 10/07/2019 23:00 CAS - CLONED(25/07/2019) Enrolled 2 10/07/2019 23:00 CAS - CLONED(25/07/2019) Enrolled 2 Anonymous
Try as a new column
Output = countx(filter(Table,Table[Name] =earlier(Table[Name]) && Table[Status] =earlier(Table[Status])),Table[Date])
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
FacilityFee Feeding Date Locationfee Duration Name Status Output FaVeFee FeedingCost Total 50 5 11/06/2019 07:00 150 2.5 CAS Enrolled 9 500 112.5 612.5 50 5 11/06/2019 07:00 150 2.5 CAS Enrolled 9 500 112.5 612.5 50 5 11/06/2019 07:00 150 2.5 CAS Enrolled 9 500 112.5 612.5 50 5 11/06/2019 07:00 150 2.5 CAS Enrolled 9 500 112.5 612.5 50 5 11/06/2019 07:00 150 2.5 CAS Enrolled 9 500 112.5 612.5 50 5 11/06/2019 07:00 150 2.5 CAS Enrolled 9 500 112.5 612.5 50 5 11/06/2019 07:00 150 2.5 CAS Enrolled 9 500 112.5 612.5 50 5 11/06/2019 07:00 150 2.5 CAS Enrolled 9 500 112.5 612.5 50 5 11/06/2019 07:00 150 2.5 CAS Enrolled 9 500 112.5 612.5 10/06/2019 08:30 2.5 PAT Enrolled 6 10/06/2019 08:30 2.5 PAT Enrolled 6 PAT Enrolled 0 19/05/2019 23:00 PAT Enrolled 1 10/06/2019 08:30 2.5 PAT Enrolled 6 10/06/2019 08:30 2.5 PAT Enrolled 6 10/06/2019 08:30 2.5 PAT Enrolled 6 10/06/2019 08:30 2.5 PAT Cancelled 1 10/06/2019 08:30 2.5 PAT Enrolled 6 50 5 10/07/2019 23:00 150 1 CAS Enrolled 2 200 10 210 50 5 10/07/2019 23:00 150 1 CA Enrolled 1 200 5 205 50 5 10/07/2019 23:00 150 1 CAS Enrolled 2 200 10 210 Final Output:
Thanks
