Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Guru's
I am hoping to get assistance please on the following.
I am having touble calculating the % share of total [Voucher Value] based on some rules.
I was thinking I would need two calculated columns or measures as follows;
1. [Total Invoice Value per File] = sum the [Voucher Value] if the [Voucher Type] ="IN" and if [File Number] is the same.
2. [% Share] = if [Voucher Order TAG] is not blank then [Voucher Value] / [Total Invoice Value per File]
[% Share] is my target field.
File Number | Voucher Number | Voucher Date | Voucher Order TAG | Voucher Type | Voucher Value | Total Invoice Value per File | % Share |
ZRTQW190000005 | 9050000164 | 08 Aug 2019 13:23:09 | YQ | 16 091 | 49 662 | ||
ZRTQW190000005 | 9050000285 | 20 Aug 2019 11:06:45 | 1 | IN | 3 728 | 49 662 | 8% |
ZRTQW190000005 | 9050000919 | 28 Aug 2019 20:44:08 | ZE | 360 | 49 662 | ||
ZRTQW190000005 | 9050001183 | 02 Sep 2019 13:16:12 | ZE | 125 | 49 662 | ||
ZRTQW190000005 | 9010001244 | 14 Sep 2019 13:32:42 | 2 | IN | 45 934 | 49 662 | 92% |
AAWTEC99847661 | 9010001246 | 14 Sep 2019 13:33:06 | 1 | IN | 4 604 | 46 031 | 10% |
AAWTEC99847661 | 9010001247 | 14 Sep 2019 13:33:38 | 2 | IN | 18 383 | 46 031 | 40% |
AAWTEC99847661 | 9040000170 | 14 Sep 2019 14:22:04 | RB | 23 268 | 46 031 | ||
AAWTEC99847661 | 9010001255 | 14 Sep 2019 14:22:30 | 3 | IN | 23 044 | 46 031 | 50% |
AAWTEC99847661 | 9050002005 | 14 Sep 2019 17:50:42 | PP | 5 472 | 46 031 |
Solved! Go to Solution.
Check :https://www.dropbox.com/s/7ludcn8uev8ewno/voucher.pbix?dl=0
Done some calculation changes.
Create both as new columns
Total Invoice=
countx(filter(table,table[File Number] = earlier(table[File Number]) && table[Voucher Type] ="IN" && earlier(table[Voucher Type])="IN"
&& table[Voucher Date Time]<=earlier(table[Voucher Date Time])),table[Voucher Value])
% Share = % Share = if(table[Voucher Type] ="IN",divide(table[Voucher Value],table[Total Invoice]),blank())
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Thanks for replying.
I think the first step is tagging the first time stamped voucher and not a total "IN" [Voucher Value] for the same file number.
could you take a look?
Total Invoice=
countx(filter(table,table[File Number] = earlier(table[File Number]) && table[Voucher Type] ="IN" && earlier(table[Voucher Type])="IN"
&& table[Voucher Date Time]<=earlier(table[Voucher Date Time])),table[Voucher Value])
Check :https://www.dropbox.com/s/7ludcn8uev8ewno/voucher.pbix?dl=0
Done some calculation changes.
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |