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
Analitika
Post Prodigy
Post Prodigy

Fixed percentage number from related table

So main table is

Name | Code | Sum | Count 

asdd | zz | 231 | 2

dfsfds | x  | 523 | 2

myt | y | 768 | 5

 

Related Table

% | Code

0,05 | x

textVal1 | y

textVal2 | z

0,05 | zz

0.1 | aa

 

 

I have measure wich calculate some percentage

measureSome% = divide(Sum, Count)

 

Then i have second measure where use this percentage

 

measureMain = [measure1] - [measure2] * measureSome%

 

Now, i need write new measure to take dinamicaly perecentage from related table if meet criteria

measureDinamicPercentage =

IF ( isDecimal(RelatedTable[%]) = True then 

RelatedTable[%]

else

if(RelatedTable[%] = textVal1 then 0.06 else [measureSome%])
)

 

 

So main measure must be
measureMain = [measure1] - [measure2] * measureDinamicPercentage

 

So have to write measureDinamicPercentage ?

1 ACCEPTED SOLUTION

Hi @Analitika 

 

this one?

measureSome% = DIVIDE(SUMX(ALL('Table'),'Table'[Sum]),SUMX(ALL('Table'),'Table'[Count]))

 006.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

6 REPLIES 6
v-diye-msft
Community Support
Community Support

Hi @Analitika 

 

If I'm correct, you might refer to these results:

measureDinamicPercentage = IF(IFERROR(VALUE(MAX('Table (2)'[%])),1)=1&&MAX('Table (2)'[%])="textVal1",0.06,IF(IFERROR(VALUE(MAX('Table (2)'[%])),1)<>1,MAX('Table (2)'[%]),[measureSome%]))

005.PNG 

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

TY for reply.

 

measureSome% = TotalCount / TotalSum

and same for each row,

 
 

 

2020-06-16_102813.png

Hi @Analitika 

 

this one?

measureSome% = DIVIDE(SUMX(ALL('Table'),'Table'[Sum]),SUMX(ALL('Table'),'Table'[Count]))

 006.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

IFERROR(VALUE(MAX('Table (2)'[%])),1)=1&&MAX('Table (2)'[%])="textVal1", 
 
is to complex as i have a lot of values to set manually, and need something like:
 
SWITCH(
SELECTEDVALUE('Table (2)'[%]),
"textVal1", Value([measureSome%]),
"textVal2", 0.06%,
VALUE('Table (2)'[%])
)
AiolosZhao
Memorable Member
Memorable Member

Hi @Analitika ,

 

What's the measure1 and measure2?

 

Please show me your desired result in a screenshot or in a table.

 

Thanks.

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




It does not matter what is measure1 and measure2, say this is 10 - 5

I cant post anything more as i dont have working measure

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.