Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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)
Solved! Go to 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 |
Hi,
You may download my PBI file from here.
Hope this helps.
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
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
Hi,
You may download my PBI file from here.
Hope this helps.
@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.
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
Hi,
In the Query Editor, ensure that all numeric columns are formatted as Decimal numbers.
@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.
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
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 |
Try as a new column
Output = countx(filter(Table,Table[Name] =earlier(Table[Name]) && Table[Status] =earlier(Table[Status])),Table[Date])
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.