advanced and costomized percent measures

[Ranahazim]

the percent is a result to a measure, the problem that it calculates a percent of each group. i want the percent to be from the totals of each row on the right

like the 6.1% is calculated from the 570000 whereas i need it from the 1920000

any tricks? the filters in measure didnt work in this case

thanks 

[v-lili6-msft]

hi, @Ranahazim

    It involves the row context and filter context in Dax, the measure in the table is calculated for the current row,

but total is the measure calculated for the whole table.

for example:

Assume that we have a basic table like this 

then we add a measure to calculate the percent that Qty of type is B in total Qty

Measure = CALCULATE(SUM(Table1[Qty]),Table1[Type]="B")/CALCULATE(SUM(Table1[Qty]))

then get the result

for periode 1 measure=5/15=33%  ,periode 2 measure=5/25=20%

but the total is (5+5)/160=6%

If you want to get the total is 53%

you need to add a further measure like this:

Measure 2 = 
var _Table = ADDCOLUMNS(SUMMARIZE(Table1,Table1[Periode],"Q",CALCULATE(SUM(Table1[Qty]))),"PER",CALCULATE(SUM(Table1[Qty]),Table1[Type]="B")/CALCULATE(SUM(Table1[Qty]))) return
CALCULATE(SUMX(_Table,[Measure]))

Result:

If it is not your case, please share pbix file or some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[Ranahazim]

Hi Again @v-lili6-msft 

your first measure worked out and thank you alot 

i tried also the second because i need both ratios but i kept getting the same as the first measure 

your measure was: Measure 2 = var _Table = ADDCOLUMNS(SUMMARIZE(Table1,Table1[Periode],"Q",CALCULATE(SUM(Table1[Qty]))),"PER",CALCULATE(SUM(Table1[Qty]),Table1[Type]="B")/CALCULATE(SUM(Table1[Qty]))) return CALCULATE(SUMX(_Table,[Measure]))

and i applied it as Measure 2 = VAR _table = (ADDCOLUMNS(SUMMARIZE('EVS Account','EVS Account'[Periode],"M",CALCULATE(SUM('EVS Account'[Credit_Limit]))),"PER",CALCULATE(SUM('EVS Account'[Credit_Limit]),'EVS Account'[Action_Code]="P")/CALCULATE(SUM('EVS Account'[Credit_Limit])))) Return CALCULATE(SUMX(_table,[Prosent of Pastdue konto benyttet kredit])) could u pls look at it? 

in measure2: i wanted the percent of a group called P of each from (  2,....12) which is loan age among all the group in each month period (Periode) which is in your excample in your previous answer : i want all the "B" percent to all other letters in each periode 1 or 2 . 

I appreciate alot the help 

best regards 

Rana 

the fisrt percentage is perfect 

In the table below . as excample of my data. the goal is to calculate the 38% percentage. of the P among age *1* and similar for each age groups 

I appreciate alot the help 

best regards 

Rana 

age	current balance	type
1	500
1	300	P
2	500	P
3	600
4	200	P
	2100
	300/(300+500)	38%

[v-lili6-msft · Accepted solution]

hi, @Ranahazim

for the measure 2, It requires a specific data structure, this measure is just for my data structure,

so please share your simple sample pbix file.

for calculate the 38% percentage. of the P among age *1* and similar for each age groups

you may try to use these two measures as below:

Measure 11 = CALCULATE(SUM(Table2[current balance]),Table2[type]="P")/CALCULATE(SUM(Table2[current balance]),ALLEXCEPT(Table2,Table2[age]))

or

Measure 12 = CALCULATE(SUM(Table2[current balance]),FILTER(Table2,Table2[type]="P"))/CALCULATE(SUM(Table2[current balance]),ALLEXCEPT(Table2,Table2[age]))

Result:

Best Regards,

Lin

[Ranahazim]

Thanks alot v-lili6-msft. 

[Greg_Deckler]

Would need to understand the source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490