How to show grouped totals in a table?

[ansa_naz]

Hi all

I have the following tables and structure:

Tables:

Sites

Lifts

Customers

Sales

They are linked as below:

Sales M --- 1 Lifts

Lifts M --- 1 Sites

Sites M ---1 Customers

Table structure is as follows:

Lifts:

LiftID     SiteID     LiftName

Sites:

SiteID     CustomerID     SiteName

Customers:

CustomerID     CustomerName

Sales:

SalesID    CustomerID    SiteID    LiftID    Amount    Date

In a table visual, I want to show the following:

SiteName             LiftName           TotalAmountForSite

So the amount column in the visual will show the total Sales for the Site, not the Lift. Is this possible using a measure somehow? Currently, if I add Sites.SiteName, Lifts.LiftName and Sales.Amount into the table, the Amount column shows the total per Lift, but I need it for Site

This is what I have now:

But I need:

SiteName      LiftName      Amount

Site 1            Lift 1             668.00

Site 1            Lift 2             668.00

Site 2            Lift 3             604.00

Site 3            Lift 4             9.54

Site 4            Lift 6             9622.50

Site 4            Lift 8             9622.50

Site 4            Lift 9             9622.50

See below PBIX file for data being used:

https://1drv.ms/u/s!AuiIgc_S9J5JhbYbCO2jbeQPfpzmXw

Cheers for all help

[v-danhe-msft · Accepted solution]

Hi @ansa_naz ,

Based on my test, you could refer to below measure:

Measure 2 = IF(ISBLANK(CALCULATE(SUM(Sales[Amount]))),BLANK(),SUMX(ALL(Sales),CALCULATE(SUM(Sales[Amount]),FILTER(ALL(Sites),'Sites'[SiteName]=MAX(Sites[SiteName])))))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[ansa_naz]

Thanks @v-danhe-msft I would never have got this solution by myself, its tricky!

[AnthonyTilley]

wasnt sure if you where just wanting the table to show site totals or a table to show site totals next to totals for lift 

eaither way i have altered your PBIX file to do what you asked.

changed the relationships in the flow 

added a calculated colunm and changed your measures 

PBIX File

[ansa_naz]

Hi @AnthonyTilley  thanks for your reply. Sadly I couldnt check what you attached as it seems to be for some other user? Not to wrry, the other reply works perfect! Cheers for your help