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.
Sample of my data]-The Total Base actually comes from a small table with year weekno and total base column which I create a relationship on year week no
yearweekno | region | Format | Sales TY | Sales LY | Total Base |
201652 | Asia | Online | 764 | 680 | 63916 |
201652 | US | Store | 856 | 795 | 63916 |
201652 | Asia | Store | 603 | 842 | 63916 |
201652 | UK | Online | 894 | 662 | 63916 |
201701 | Asia | Online | 925 | 845 | 89125 |
201701 | US | Store | 837 | 776 | 89125 |
201701 | Australia | Online | 845 | 675 | 89125 |
201701 | UK | Store | 793 | 799 | 89125 |
201702 | Australia | Store | 659 | 678 | 98456 |
201702 | UK | Online | 981 | 592 | 98456 |
201702 | Asia | Store | 514 | 520 | 98456 |
201702 | US | Online | 648 | 964 | 98456 |
The measure I want to calculate is %Share(of base)=(Sales TY-Sales LY)/Total Base
If I use a measure in PowerBI for this it would look like
%Share= (SUM('Table'[SalesTY])-sum('Table'[SalesLY]))/min('Table'[Total Base])
Even if I use max it wouldn't be different at week no levels
%Share= (SUM('Table'[SalesTY])-sum('Table'[SalesLY]))/max('Table'[Total Base])
It would look like the below table which would be same for min or max in the denominator since it would take only value to divide as it should
yearweekno | Sales TY | Sales LY | %Share |
201652 | 3117 | 2979 | 0.22% |
201701 | 3400 | 3095 | 0.34% |
201702 | 2802 | 2754 | 0.05% |
The problem arises when you use a 'card' or only one week-it takes the total row and the data looks like this
201702 | 2802 | 2754 | 0.0% |
Total | 2802 | 2754 | 0.1% |
This (0.1% i.e incorrect) takes the min base of the entire column when it should have taken only 201702
There is no way I can change that in card (as in donot show totals or something)
The problem might arise again if the slicer has only one week no and region level %share is calculated.
How do I arrive at a formula that shall help me get the corresponding denominator to divide in the %share calculation as min max donot work.
I have some pages with weekly line charts and %share as well as pages where it is just a week slicer with region %share
How do I make sure the formula works universally?
Solved! Go to Solution.
Sorry for the late reply!
I was able to solve the issue.The issue was not related to the formulas but it was related to the data connections and relationships between my tables which I had made between tables which was duplicating the dimension metric and hence max(base) was changing.
Thanks for looking into it though.Much appreciated.
Hi @p_kaushal,
According to your description above, I have tested it with your sample data and the formulas. I am not sure I can reproduce your issue. It seems to work all fine for me.
%Share = ( SUM ( 'Table1'[Sales TY] ) - SUM ( 'Table1'[Sales LY] ) ) / MAX ( 'Table2'[Total Base] )
Here is the sample pbix file for your reference.
Regards
Thanks for having a look.Yes, that's how it should work.Somehow the total row % share doesn't show correct values in my Power BI.
It's a weird error !
Hi @p_kaushal,
Yes, a little weird. Could you share a sample pbix file which can reproduce the issue? So that I can help further investigate on this issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards
Sorry for the late reply!
I was able to solve the issue.The issue was not related to the formulas but it was related to the data connections and relationships between my tables which I had made between tables which was duplicating the dimension metric and hence max(base) was changing.
Thanks for looking into it though.Much appreciated.
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 |
---|---|
113 | |
103 | |
77 | |
67 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
81 | |
68 |