Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I currently have this set of database:
I would like to create the following view in Power BI where I can compare the gross profit and operating for states regions and products at once.
Expected Results:
The gross and operating profit are calculated measures in Power BI with following formulas:
I would need another replica of these filters and cards and find difference when slicers are selected. I cannot duplicate the tables as my actual work has about 50 calculated fields to achieve the operating profit so would like to seek for some expert opinions without the need of duplicating another table.
Please find the links to the excel and power bi file here:
https://drive.google.com/drive/folders/1_iiFNWrVGAyAhHY5hWnSbETcNGygRMYJ?usp=sharing
Thanks!
Thanks!
Solved! Go to Solution.
Hi @milkywaypowerbi ,
I updated the sample pbix file(see attachment) again, please check whether that is what you want.
1. Update the formula of measure [Gross Profit1] and [Gross Profit2]as below
Gross Profit1 = VAR _selstate1=SELECTEDVALUE('Slicer1'[State]) VAR _selregion1=SELECTEDVALUE('Slicer1'[Region]) VAR _selproduct1=SELECTEDVALUE('Slicer1'[Product ]) VAR _grossprofit1 =CALCULATE([Gross Profit],FILTER('Sheet2','Sheet2'[State]=_selstate1&&'Sheet2'[Region]=_selregion1&&'Sheet2'[Product ]=_selproduct1)) RETURN _grossprofit1 |
Gross Profit2 =
VAR _selstate2=SELECTEDVALUE('Slicer2'[State])
VAR _selregion2=SELECTEDVALUE('Slicer2'[Region])
VAR _selproduct2=SELECTEDVALUE('Slicer2'[Product ])
VAR _grossprofit2 =CALCULATE([Gross Profit],FILTER('Sheet2','Sheet2'[State]=_selstate2&&'Sheet2'[Region]=_selregion2&&'Sheet2'[Product ]=_selproduct2))
RETURN _grossprofit2
|
2. Please don't create any relationship between slicer tables and fact table(Sheet2)
Best Regards
Hi @milkywaypowerbi ,
I updated your sample pbix file(see attachment), please check whether that is what you want.
1. Create two dimension table and use the fields in these two tables as slicer options
Slicer1 = SUMMARIZE('Sheet2','Sheet2'[State],'Sheet2'[Region],'Sheet2'[Product ])
Slicer2 = SUMMARIZE('Sheet2','Sheet2'[State],'Sheet2'[Region],'Sheet2'[Product ])
2. Create multiple measures to get the gross profit, Operating profit and the differences
Gross Profit1 =
VAR _selstate1=SELECTEDVALUE('Slicer1'[State])
VAR _selregion1=SELECTEDVALUE('Slicer1'[Region])
VAR _selproduct1=SELECTEDVALUE('Slicer1'[Product ])
VAR _grossprofit1 =CALCULATE(SUM(Sheet2[Revenue])-SUM(Sheet2[Product Cost])-sum(Sheet2[Warranty]),FILTER('Sheet2','Sheet2'[State]=_selstate1&&'Sheet2'[Region]=_selregion1&&'Sheet2'[Product ]=_selproduct1))
RETURN _grossprofit1
Gross Profit2 =
VAR _selstate2=SELECTEDVALUE('Slicer2'[State])
VAR _selregion2=SELECTEDVALUE('Slicer2'[Region])
VAR _selproduct2=SELECTEDVALUE('Slicer2'[Product ])
VAR _grossprofit2 =CALCULATE(SUM(Sheet2[Revenue])-SUM(Sheet2[Product Cost])-sum(Sheet2[Warranty]),FILTER('Sheet2','Sheet2'[State]=_selstate2&&'Sheet2'[Region]=_selregion2&&'Sheet2'[Product ]=_selproduct2))
RETURN _grossprofit2
Difference of Gross Profit = [Gross Profit1]-[Gross Profit2]
Operating Profit1 =
VAR _selstate1=SELECTEDVALUE('Slicer1'[State])
VAR _selregion1=SELECTEDVALUE('Slicer1'[Region])
VAR _selproduct1=SELECTEDVALUE('Slicer1'[Product ])
VAR _investment1 =CALCULATE(SUM(Sheet2[Investment ]),FILTER('Sheet2','Sheet2'[State]=_selstate1&&'Sheet2'[Region]=_selregion1&&'Sheet2'[Product ]=_selproduct1))
RETURN
[Gross Profit1]-_investment1
Operating Profit2 =
VAR _selstate2=SELECTEDVALUE('Slicer2'[State])
VAR _selregion2=SELECTEDVALUE('Slicer2'[Region])
VAR _selproduct2=SELECTEDVALUE('Slicer2'[Product ])
VAR _investment2 =CALCULATE(SUM(Sheet2[Investment ]),FILTER('Sheet2','Sheet2'[State]=_selstate2&&'Sheet2'[Region]=_selregion2&&'Sheet2'[Product ]=_selproduct2))
RETURN
[Gross Profit2]-_investment2
Difference of Operationg Profit = ([Gross Profit1]-[Operating Profit1])-([Gross Profit2]-[Operating Profit2])
Best Regards
Thank you for your solution. However, is it possible to use the calculated measure directly in the formula instead of repeating the formula in the measure? I have a gross profit measure written already.
The issue is that I have quite a a lot of calculated measure in my actual work which affects subsequent calculated measure so I would like to check if I can use calculated measure in Var _grossprofit instead of SUM(Sheet2[Revenue])-SUM(Sheet2[Product Cost])-sum(Sheet2[Warranty]).
Another issue is that I have formed relationship between the database and one slicer table so the second formula is still reacting to the first slicer table since there are relationship
Thanks in advance
Hi @milkywaypowerbi ,
I updated the sample pbix file(see attachment) again, please check whether that is what you want.
1. Update the formula of measure [Gross Profit1] and [Gross Profit2]as below
Gross Profit1 = VAR _selstate1=SELECTEDVALUE('Slicer1'[State]) VAR _selregion1=SELECTEDVALUE('Slicer1'[Region]) VAR _selproduct1=SELECTEDVALUE('Slicer1'[Product ]) VAR _grossprofit1 =CALCULATE([Gross Profit],FILTER('Sheet2','Sheet2'[State]=_selstate1&&'Sheet2'[Region]=_selregion1&&'Sheet2'[Product ]=_selproduct1)) RETURN _grossprofit1 |
Gross Profit2 =
VAR _selstate2=SELECTEDVALUE('Slicer2'[State])
VAR _selregion2=SELECTEDVALUE('Slicer2'[Region])
VAR _selproduct2=SELECTEDVALUE('Slicer2'[Product ])
VAR _grossprofit2 =CALCULATE([Gross Profit],FILTER('Sheet2','Sheet2'[State]=_selstate2&&'Sheet2'[Region]=_selregion2&&'Sheet2'[Product ]=_selproduct2))
RETURN _grossprofit2
|
2. Please don't create any relationship between slicer tables and fact table(Sheet2)
Best Regards
@milkywaypowerbi , we need an independent table for the second set of slicers
example for the two date slicer - How to use two Date/Period slicers: https://www.youtube.com/watch?v=WSeZr_-MiTg
Check
Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |