cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OliverS Frequent Visitor
Frequent Visitor

New measure: create a SUM and Groupings

Hi,

 

I'm totally new to PowerBI and dax and I'm using the Contoso sample file.

 

From the following grid, I'd like to add a new measure that would Sum SalesAmount for each Store (StoreName and SalesAmount are coming from 2 different - but linked - tables):

2017-03-13_16-33-25.png

  

Goal is to get the contribution of each ProductCategory to StoreTotal.

Any advise would be warly welcome!

 

Thanks,

 

Oliver

3 REPLIES 3
JaredK Member
Member

Re: New measure: create a SUM and Groupings

Hi @OliverS, just to confirm, is SalesAmount a measure or column? And you want to show the full sales amount for the store on each row? If I understand you correctly, here's how I would approach this:

 

 

Sales = SUM ( SalesTable[SalesAmount] )

 

Store Sales =
IF (
    HASONEVALUE ( StoreTable[StoreName] ),
    CALCULATE ( Sales, ALL ( [ProductCategory] ) )
)

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net
OliverS Frequent Visitor
Frequent Visitor

Re: New measure: create a SUM and Groupings

Hi Jared,

 

Yes, SalesAmount is a column.

When trying this approach, the validation error message is "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Still puzzled to see how such an important feature in a BI report is hard to do.

 

Thanks for your help!

 

Olivier

v-huizhn-msft Super Contributor
Super Contributor

Re: New measure: create a SUM and Groupings

Hi @OliverS,

Have you resolve your issue? If you have resolved it, please mark the helpful reply as answer, if you haven't, please feel free to ask.

Best Regards,
Angelia