Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anno2019
Helper IV
Helper IV

Calculate % Share based on specific column contents and values

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 NumberVoucher NumberVoucher DateVoucher Order TAGVoucher TypeVoucher ValueTotal Invoice Value per File% Share
ZRTQW190000005905000016408 Aug 2019 13:23:09 YQ16 09149 662 
ZRTQW190000005905000028520 Aug 2019 11:06:451IN3 72849 6628%
ZRTQW190000005905000091928 Aug 2019 20:44:08 ZE36049 662 
ZRTQW190000005905000118302 Sep 2019 13:16:12 ZE12549 662 
ZRTQW190000005901000124414 Sep 2019 13:32:422IN45 93449 66292%
AAWTEC99847661901000124614 Sep 2019 13:33:061IN4 60446 03110%
AAWTEC99847661901000124714 Sep 2019 13:33:382IN18 38346 03140%
AAWTEC99847661904000017014 Sep 2019 14:22:04 RB23 26846 031 
AAWTEC99847661901000125514 Sep 2019 14:22:303IN23 04446 03150%
AAWTEC99847661905000200514 Sep 2019 17:50:42 PP5 47246 031 

 

 

1 ACCEPTED SOLUTION
4 REPLIES 4
amitchandak
Super User
Super User

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

@amitchandak 

 

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])

@amitchandak 

 

Works peeeerrrrfffectly!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.