Get a value to apply as a filter

DAX DAX Calculations
1

I already tried to solve the problem using three different approachs.
. Using the LOOKUPVALUE function
. Using an X function after using the SUMMARIZE function
. Using an X function after using the FILTER function with the ALL

I wasn´t able to get what I want.
Could you give me some clues that can help me?
The attachment image shows what I have and what I want to obtain.

exercice
exercice1041×381 43 KB

Thanks

2

@JoaoMonteiro,

What you’re trying to do shouldn’t be that difficult, but it’s impossible to answer this question without seeing your PBIX file since the DAX to calculate the result you want is entirely dependent on the way you’ve built you data model.

If you can please post your PBIX file, I’m sure you’ll get a quick and specific solution to your problem (probably more than one…).

Thanks.

  • Brian
3

Hi @JoaoMonteiro, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

4

Hi @BrianJ

I really appreciate any help that could lead me to understand the issue.
I’m sending my .pbix file.

Schedule data model.pbix (2.2 MB)

5

@JoaoMonteiro,

Thanks for posting the PBIX. I’ll dig into this at lunch and should have a solution back to you later today.

  • Brian
6

@JoaoMonteiro,

I created a random sales figure in the Schedule fact table to better mimic the request in your original post. Here are the three key measures I used:

Total Sales by Cost Ctr = 
CALCULATE(
    [Total Sales],
    ALLEXCEPT(
        Convex,
        Convex[Cost Center]
    )
)

Total Sales by Agent = 
CALCULATE(
    [Total Sales],
   FILTER(
       Convex,
       Convex[Name] = [Harvest Agent]
   )
) 

% of Total Sales = 
DIVIDE(
    [Total Sales by Agent],
    [Total Sales by Cost Ctr],
    BLANK()
)

Here’s what it looks like all put together:

image

I hope this is helpful. Full solution file attached below.

– Brian

eDNA Forum - Schedule data model solution.pbix (2.3 MB)

7

@BrianJ

Thank you so much for your help.
After I look at it I felt myself a little bit dummy. :roll_eyes:
I believe that is the cost I’ve to pay for being a newbie.

You’re are great!

8

Not at all! I still do this exact same thing myself - mentally jump to a much more complex approach than necessary, overlooking a much simpler solution. Sometimes it just takes someone to look at the problem with fresh eyes, which is what the forum is great for.

Glad I was able to help.

  • Brian