Grouping at Different Levels

Anonymous · ‎07-09-2019

Hi Guys,

I have attahced the link to my PBIX file and my requiremet is to populae the subcategory total sales to calculat the percentage.

1. First i need to calculate Average at Item level(Average of multiple weeks at item level).

2. Sum of Average item to Subcategory level

3. I will not use Subcategory in my Table report.

I am expecting Subtotal of Subcategory as

450 for Individaul Person

600 for Legal Person

Currently it is 900 and 1200.

https://drive.google.com/file/d/1oJ7-_hzOUOMo8yBbpZJm20f1BA4c35mP/view?usp=sharing

parry2k · ‎07-09-2019

@Anonymous change your Total Sales measure as below

Total Sales = 
SUMX( VALUES( SalesInfo[ProductName]), CALCULATE(Average( SalesInfo[SalesAmount] ),
WeekCal[YearNum]<=201914,WeekCal[YearNum]>=201913))

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎07-09-2019

Thanks for you Quick reply .

I want to calculate Item percentage contribution to Subcategory level and I don’t want to show Subcategory in my report. I want to see 450 and 600 in the place of 900 and 1200 in my table chart to calcuate the same.

parry2k · ‎07-09-2019

@Anonymous change your grand sales measure as below

Grand Total of Sub Sales = SUMX( ALLSELECTED( SalesInfo[ProductName] ), [Total Sales] )

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎07-09-2019

Thanks for You reply,

I have attahced the output of your experssion and i am getting 1050 as output . As i expalined earlier ,

I want to calculate Item percentage contribution to Subcategory level and I don’t want to show Subcategory in my report. I want to see 450 and 600 in the place of 900 and 1200 in my table chart.