TotalCost based on Table or Matrix Value

[moeconsult]

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)

@Ashish_Mathur @Greg_Deckler 

[moeconsult · Accepted solution]

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

@amitchandak 

[amitchandak]

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

[moeconsult]

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: 

@amitchandak 

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[moeconsult]

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

[Ashish_Mathur]

Hi,

You may download the PBI file from here.

Hope this helps.

[moeconsult]

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

[Ashish_Mathur]

Hi,

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

[moeconsult]

---

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

[Ashish_Mathur]

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.

[moeconsult]

@Ashish_Mathur  please see link below:

https://1drv.ms/u/s!AgOkXVsYoRSemxgpbhypz3N1VYEE?e=Xy9EVg 

[moeconsult]

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

[moeconsult · Accepted solution]

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

@amitchandak 

[amitchandak]

@moeconsult 

Try as a new column

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