Power BI : Dynamic aggregations and Drillthrough

[Anonymous]

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

[amitchandak · Accepted solution]

@Anonymous , Try like

if(calculate(sum(Transaction[Buyer Utilization]),allexcept(Transaction[Buyer])) - max(Entity[Single group Utilization])<0, "Breached", "Non Breached")

[Anonymous]

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.

[v-shex-msft]

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