Forum Discussion

Devender9999's avatar
Devender9999
Frequent Visitor
2 years ago

different Subtotal at drill up and down level

How can I implement conditional aggregation in Power BI to generate different subtotals based on different drill-down levels within a matrix visual? For Example,
suppose I have sales data categorized by 'Region' and 'Country.'

When drilling down from 'Region' to 'Country,'
I want the subtotal to include only sales from specific 'Countries' in a particular 'Region.'
However, when viewing the total at the 'Region' level, it should display the overall subtotal for all 'Countries' in that 'Region.'

Is there a method, possibly using DAX measures or any other approach in Power BI, to achieve this varying subtotal display based on different drill-down levels?"


example 
when all expand drill

region
India    Usa sub total
10        30      40

when drill up 
region 
40 

 

this is how things will look like but i need that when it is drill up which is region only it should show different value based on cond 
let say 50 

so it should be 50 not 40

2 Replies

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion

    Hi Devender9999 

    Please share sample data or a sample pbix file and screenshot of the expected result.

    If solved your requirement, please mark this answer as SOLUTION.
    If this comment helps you, appreciate your KUDOS

    Thanks
    Pijush


  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Devender9999,

    Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly  

    BTW, it sounds like a common multiple aggregation level calculation issue, you can also take a look a the following blog to know how to handle this scenario:

    Measure Totals, The Final Word  

    Regards,

    Xiaoxin Sheng