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.
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
Solved! Go to 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
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
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
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% |
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
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
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 |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |