Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mshahid
Regular Visitor

GrandTotal & SubTotal and Changing Bases of Measure

Hi All,

 

So I am new to this so if my question is very basic please bear with it. 🙂

 

I have two fact tables; one 'Sales' with product wise number of bills and the other 'Visits' with total number of bills. Both are connected to a territory dimension table; while 'Sales' is linked with a product dimension table. I am trying to calculate product bill to total bills ratio; which is quite simple. I created a measure:

TotalProd:=CALCULATE(SUM(Visit[Productive])) in Visit table

PrdBills:=CALCULATE(SUMSales([VisitCount])) in Sales table.

 

To find that ratio I simply created the following measure

BrandProductivity:=CALCULATE([PrdBills]/[TotalProd])

 

This works fine; however, the problem comes when one or two of the products are only sold in certain territories; therefore the [TotalProd] measure needs to exlude the total number of bills for those territories where it is not sold to calculate the ratio. Otherwise, when subtotaling the data on various territory dimensions; the subtotals comes out to be messed up; see below:

 

Capture.PNG

Here, I have product dimension in columns and one territory dimension in rows. However, the ratio for Prd1 and Prd2 is miss leading as you can see in the highlighted cells. To handle this I first created two support tables listing the territories where only Prd1 and Prd2 is sold and added them to the PowerPivot. Then I linked them with the territory dimension table and added 2 custom columns to identify which territory only sold Prd1 or Prd2.

Column1 [Prd1Only]=LEN(RELATED(Prd1Only[Prd1Only]))>0

Column2 [Prd2Only]=LEN(RELATED(Prd2Only[Prd2Only]))>0

 

This gives me a boolean value for each column stating it that is only Prd1 territory or only Prd2 territory. Then I added two more measures in Visit table:

TotalPrd1Productive:=CALCULATE([TotalProd],Territory[Prd1Only]=TRUE())

TotalPrd2Productive:=CALCULATE([TotalProd],Territory[Prd2Only]=TRUE())

 

And then calculated product bill to total bills ratio individually for each product:

Prd1:=CALCULATE([PrdBills]/[TotalPrd1Productive],Product[ProductCode]="Prd1")

Prd2:=CALCULATE([PrdBills]/[TotalPrd2Productive],Product[ProductCode]="Prd2")

Prd3:=CALCULATE([PrdBills]/[TotalProd],Product[ProductCode]="Prd3")

Prd4:=CALCULATE([PrdBills]/[TotalProd],Product[ProductCode]="Prd4")

Prd4:=CALCULATE([PrdBills]/[TotalProd],Product[ProductCode]="Prd4")

 

This is the result I get

Capture.PNG

 

Now this makes sense. The grand total and even the subtotals. However, the problem here is the following:

1. Whenever a new products gets added; I have to add a new ratio manually.

2. The rows have one of the territory dimension however, the columns are just each of the above individually calculated ratios and I cannot link it with product dimension; if I do; I get repeated values with blanks or repeated values for each product for which that measure is not being calculated. Any help in this regard will be highly appreciated. I also have a mockup excel file for this if any of you need to work upon; However, I dont know whaat are the forum's rules for sharing files. Any help in this regard will be appreciated.

8 REPLIES 8
dearwatson
Responsive Resident
Responsive Resident

Hi mshahid,

 

Edit -- try Matt's solution above first - he's smarter than me.

 

Yep this happens, you will need to manually fix the totals due to the way the DAX engine calculates the measures.

 

Create a new measure called BrandProductivity2:

AVERAGEX(VALUES(Table1[Territory]),[BrandProductivity])

Drag it into the pivot and check the results.

This should correct the grand totals using the AVERAGEX Iterator... the explananation for this would take longer than I have but let me know how you go.

 

Cheers

Greg

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

Firstly, your first 2 measures don't need calculate

TotalProd:=SUM(Visit[Productive]) in Visit table
PrdBills:=SUMSales([VisitCount]) in Sales table.

 

Now try this (untested but I think it will work)

BrandProductivity:=calculate([PrdBills]/[TotalProd],Sales)

 

This is an easy to read formula but it is very complex under the hood. If you want to learn a out how it works, read my article here (particularly about expanded tables) http://exceleratorbi.com.au/many-many-relationships-dax-explained/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

MattAllington: This works when there is only one level of hirearchy in territory diemnsion. Howevere, when there are more; it messes up the calculation showing ~100% for all products. Also with more hirearchy in territroy diemsion; it takes a lot of time load.

 

deatwatson: I liked your solution and it is giving me a results very close to what I expect I have to do a bit of testing for the results as to what is causing the difference. Will get back to you soon.

 

Meanwhile; I am thinking on line of making another support table with territory wise products list; do you guys think it will work? I am pulling data off a SQLdB; and I can work my way to create a result of territory wise product lists. If so; can someone tell me how will I have to manipulate the measures.

 

 

Alternately; if someone can help me improve the following formula which is currently giving me error I'd appreciate it too:

BrandProductivity:=CALCULATE([PrdBills]/IF(Sales[ProductCode]="Prd1",[TotalPrd1Productive],IF(Sales[ProductCode]="Prd2",[TotalPrd2Productive],[TotalProd])))

 

This is the closing I can get to create a formula where it changes the demoninator based on ProductCode.

Hi mshahid,

 

I can pretty definitively say there is an easier way to achieve this but to do so we may need to check the format of the source data and create properly formatted measures (it will help me unpick the complexity)

 

can you please post the sample file. I'll check it out.


Cheers

Greg

 

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

Can someone please help me post a file here? I have a sample file here which can help you guys understand the problem and propose a solution more conveniently.

Just use a link to Dropbox or Onedrive



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

I have taken a look, but I don't understand your data.  You have hard coded the brand8 and 9 data into tables, but how did you do that?  How do you know that these products are sold or not sold from the other tables?  Also, can you set up a number of pivot tables that show the raw data (Numerator and Denominator) each in their own pivot clearly showing how the cell by cell calculation should work



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.