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.
Hi, I have a specific requirement where from single transaction table I have to develop 3 hierarchical/drillthrough reports.
Below are my input tables.
And below are my 3 reports format.
1st one is at 'Group' Level
2nd is at 'Buyer' level which is a drill through from above report
The 3rd is at 'Loan' level which is a drill through from above report
My main problem areas are on the fly calculation of 'Balance' and 'Status' column. Which by nature are categorical but needs to be calculated via a measure (hence dynamic in nature).
One approach I had thought of was while fetching the data only do Group level and Buyer level aggregations but data is huge and the field 'Utilization' itself is a calculated field I have generated inside Power BI.
Please help me how to achieve above reports.
Please let me know if more info is required for the problem. @Greg_Deckler @v-zhenbw-msft
Solved! Go to Solution.
@Anonymous , Try like
if(calculate(sum(Transaction[Buyer Utilization]),allexcept(Transaction[Buyer])) - max(Entity[Single group Utilization])<0, "Breached", "Non Breached")
@Anonymous , Try like
if(calculate(sum(Transaction[Buyer Utilization]),allexcept(Transaction[Buyer])) - max(Entity[Single group Utilization])<0, "Breached", "Non Breached")
Hi @amitchandak , the calculate function part is giving me the proper sum of utilization, BUT the MAX part is always giving the Maximum Limit from that Table irrespective of the Entity. I need Max Limit for that particular Entity. I tweaked your formula to Calculate(Max(Limit), ALLEXCEPT(Table, Entity)), but still it gives me overall Max of Limit irrespective of Entity.
Below is the snapshot of my Entity Master table from where Entity Limit needs to be pulled. Can you please help me with it.
Hi @Anonymous,
You a refer to the following link to know to how to check current row content and write a format to extract current category as a parameter to calculate on the total level:
Clever Hierarchy Handling in DAX
Regards,
Xiaoxin Sheng
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |