Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am still a beginner but I have the following use case and I am not sure if and how it is possible to set it up:
Let's say I have the following table:
Actual Revenue | Plan Revenue | |
Project A | 10 | 9 |
Project B | 8 | 9 |
Project C | 5 | 5 |
I have then added a measure that calculates the Actual vs Plan percentage.
Our users are not really interacting with the dataset in Power BI directly, but instead in Excel through Data -> Get Data -> From Power Platform -> From Power BI as a pivot table.
But now we have an addition of a second plan. So the table is now:
Actual Revenue | Plan 1 Revenue | Plan 2 Revenue | |
Project A | 10 | 9 | 13 |
Project B | 8 | 9 | 11 |
Project C | 5 | 5 | 3 |
Ideally, I would like to avoid having 2 separate measures like Actual vs Plan 1 and Actual vs Plan 2, and just have one measure, using the Plan value based on the users, if he wants to use Plan 1 or Plan 2.
Is there a way to do this, so the user can use it in excel's pivot tables as well?
Thanks!
Solved! Go to Solution.
Hi @Hambach ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Select the [Plan 1 Revenue] column and the [Plan 2 Revenue] column in the power query editor to transpose.
(3)We can create a measure.
Plan percentage =
DIVIDE(SUM('Table'[Revenue]),SUM('Table'[Actual Revenue]),0)
(4) Filter the [Plan] field in the pivot tables.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Hambach ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Select the [Plan 1 Revenue] column and the [Plan 2 Revenue] column in the power query editor to transpose.
(3)We can create a measure.
Plan percentage =
DIVIDE(SUM('Table'[Revenue]),SUM('Table'[Actual Revenue]),0)
(4) Filter the [Plan] field in the pivot tables.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @Hambach
you shall know a power feature called field parameter, with which the user could decide which measure to plot in a visual. But it works only on Power BI.
https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |