Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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])
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |