cancel
Showing results 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

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)

 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)

2 ACCEPTED SOLUTIONS
Helper V

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

 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

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
13 REPLIES 13
Super User

Ideally, you should unpivot the table.

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]))

Helper V

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

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper V

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

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper V

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 canvas

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper V

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

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper V

Helper V

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

Helper V

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

 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

Super User

Try as a new column

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