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
Anonymous
Not applicable

ADD COLUMN DIVIDE-SUM-FILTER

Hello All, 

 

I want to add column of monthly budget calculate from yearly budget  filter with multiple rows same prduct categeory  which are same with number of months. 

 

I used follwing dax to add column it is not working. 

 

Monthly forecast =DIVIDE(SUM(Sales[Budget]),DISTINCTCOUNT(Sales[Brand]))
 
Thanks in advance. 
 
SweZin 
 
 
1 ACCEPTED SOLUTION

Hi @Anonymous 

 

The error is the result of ALLSELECTED being added as 3 arguments of DIVIDE and it should be in CALCULATE, try this.

Monthly forecast = 
CALCULATE(
    DIVIDE(
        SUM( Sales[Budget] ),
        COUNT( Sales[Month] )
    ),
    ALLEXCEPT( Sales, Sales[Brand] )
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

Please provide some dummy data with similar data structure and expected results to help us clarify your requirement and test to coding formula on it.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Try.

Monthly forecast = 
CALCULATE(
    DIVIDE(
        SUM( Sales[Budget] ),
        DISTINCTCOUNT(Sales[Brand] )
    )
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Anonymous
Not applicable

Hello @Mariusz , 

 

There is no error but data is wrong. 

I changed as followed and there is error.

 

Monthly forecast =
CALCULATE(
DIVIDE(
SUM(Sales[Budget ),
COUNT(
Sales[Month]),
ALLEXCEPT(
Sales,Sales[Brand])
)
)
 

Best Regards, 

SweZin 

 

 

 

Hi @Anonymous 

 

The error is the result of ALLSELECTED being added as 3 arguments of DIVIDE and it should be in CALCULATE, try this.

Monthly forecast = 
CALCULATE(
    DIVIDE(
        SUM( Sales[Budget] ),
        COUNT( Sales[Month] )
    ),
    ALLEXCEPT( Sales, Sales[Brand] )
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

@Anonymous 

are you creating a measure? Maybe you can try

Monthly forecast =

DIVIDE(CALCULATE(SUM(Sales[Budget},ALLEXCEPT(Sales,Sales[Brand])),DISTINCTCOUNT(Sales[Month] ))





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.