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.
better i move directly to an example to be clear with my query. here Result is a measure column which i have created which is nothing but division of (Arrear/Target) column
Input table to Power Bi:
Zone District School Subject Arrear Target Result
1 A Noon Science 20 10 2
1 A Noon Commerce 30 5 6
1 A Noon Arts 20 4 5
1 B Evening Maths 40 5 8
1 B Evening PhysicsA 20 4 5
1 B Noon Chem A 30 3 10
1 B Noon BIO A 40 8 5
1 B Evening Social 30 10 3
2 C Evening Moral 20 4 5
2 C Noon GK 30 3 10
2 C Noon EVS 40 8 5
2 D Evening History 30 10 3
2 D Evening Geo 30 10 3
Now my output should be like if I want to cumalate on Zone, District and School. It Should Be LIke When I have only Zone
Zone Arrear Target Result
1 230 49 2.58
2 150 26 5.76
If I want District wise
Zone District Arrear Target result
1 A 70 19 3.68
1 B 160 30 5,33
2 c 90 11 6.36
2 D 60 20 3
So In short If I select Zone, Then I should get a cumalative of Arrear and Target column for each zone and their divison
and if I select District, I should get a cumalative of Arrear and Target column for each district in each zone and then their divison.
So please let me know If any thing is not clear from my end and I can elaborate more and how should I acheive it.
Thanks!!!
Solved! Go to Solution.
Hi @Anonymous,
You can create two measures:
Result1 = DIVIDE(CALCULATE(SUM(Table1[Arrear]),ALLEXCEPT(Table1,'Table1'[Zone])),CALCULATE(SUM(Table1[Target]),ALLEXCEPT(Table1,Table1[Zone])))
Result2 = DIVIDE(CALCULATE(SUM(Table1[Arrear]),ALLEXCEPT(Table1,'Table1'[Zone],Table1[District])),CALCULATE(SUM(Table1[Target]),ALLEXCEPT(Table1,Table1[Zone],Table1[District])))
You can see attached pbix file.
Best Regards,
Qiuyun Yu
Hi,
Try this. Delete the Result column from your source data. Write the following measures:
Total arrears=SUM(Data[Arrear])
Total target=SUM(Data[Target])
Result=[Total arrears]/[Total target]
Create a simple Table visual and drag any/all of Zone/District/School.
Hope this helps.
Hi,
Try this. Delete the Result column from your source data. Write the following measures:
Total arrears=SUM(Data[Arrear])
Total target=SUM(Data[Target])
Result=[Total arrears]/[Total target]
Create a simple Table visual and drag any/all of Zone/District/School.
Hope this helps.
Hi @Anonymous,
You can create two measures:
Result1 = DIVIDE(CALCULATE(SUM(Table1[Arrear]),ALLEXCEPT(Table1,'Table1'[Zone])),CALCULATE(SUM(Table1[Target]),ALLEXCEPT(Table1,Table1[Zone])))
Result2 = DIVIDE(CALCULATE(SUM(Table1[Arrear]),ALLEXCEPT(Table1,'Table1'[Zone],Table1[District])),CALCULATE(SUM(Table1[Target]),ALLEXCEPT(Table1,Table1[Zone],Table1[District])))
You can see attached pbix file.
Best Regards,
Qiuyun Yu
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 |
---|---|
113 | |
97 | |
79 | |
74 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |