Measure based on dividing on types

[MikeHendriks]

Hi all,

I have a PBIX file with a fact table with numbers in it. These are linked to dimensions like costcenter.

But now I have an issue with my measure on total level because I have a special wish. This is my output:

This looks ok, but I have 1 special thing; the columns Number and Subtype are coming from Costcenter. And when I have Subtypes NXO ánd VXO (like on number 12) I don't want the total to sum up these, but just use it only once. So total in this example must be 290, because number 12 = 90, number 100 = 100 and 23 = 100.

My current Capacity 2 measure is:

Capacity 2 measure =

VAR CurDate = MAX('Calendar'[Date])

VAR CurDateMin = MIN('Calendar'[Date])

VAR x = CALCULATE(

    SUM('Fact'[Capacity 2]),

    NOT ISBLANK( 'Fact'[Capacity 2])

    && OR(CurDate <= 'Fact'[Enddate], CurDateMin <= 'Fact'[Enddate])

    && OR(CurDate >= 'Fact'[Startdate], CurDateMin >= 'Fact'[Startdate])

)

RETURN x

I've tried many things like SUMMARIZING costcenter on Number and subtype and so on, but every time I get stuck.

Is there a DAX crack here that can help me with the solution?

PBIX example can be found here: https://file.io/nfbQbg5K6Dgx

[MikeHendriks]

Hi @Ahmedx,

Thank you for your quick response! This seems to work for the data that is in the example, but I saw on your code that it's not working for cases where only VXO is present (and NXO is not). I've added that case to https://easyupload.io/1j4mpj with costcenter 567.

Is there an option which is working not with an approach where VXO is not calculated, but just 1 value for VXO and NXO when both are present. They will by the way always be equal.

[Ahmedx]

I can’t help you, you better get a new version on your personal computer and see my solution

[MikeHendriks]

I have downloaded your file @Ahmedx. It works I think, but 

SWITCH(TRUE(),

MAX('Costcenter'[Type])="XXO"&&MAX('Costcenter'[SubType])="VXO",0,[Capacity 2 measure])

Gives issues in a case where VXO is present, but NXO is not.

[Ahmedx]

in such a case what should we do?

[MikeHendriks]

If VXO = 80 and NXO is not present -> 80
If VXO is not present and NXO = 80 -> 80
If VXO = 80 and NXO = 80 -> 80

[Ahmedx]

try

[MikeHendriks]

Unfortunately @Ahmedx, this is not working. With my new added 'VXO only' example you get this:

While I was expecting that 70 to be added here, so 360 in total.

In my perspective this measure is wrong while it has 80 hardcoded:

Capacity 3 measure = 
 VAR _t1 = MAX('Costcenter'[Number])
VAR _t2 =FILTER(ALL(Costcenter),'Costcenter'[Number]=_t1&&'Costcenter'[SubType]="VXO")
VAR _t3 = FILTER(ALL(Costcenter),'Costcenter'[Number]=_t1&&'Costcenter'[SubType]="NXO")
VAR _t4 =CALCULATE([Capacity 2 measure],_t2)
VAR _t5 =CALCULATE([Capacity 2 measure],_t3) 
 VAR _result = 
  SWITCH(TRUE(),
   ISBLANK(COUNTROWS(_t2)) && _t5=80,80,
   ISBLANK(COUNTROWS(_t3)) && _t4=80,80,
   _t4=80&&_t5=80,80,
        MAX('Costcenter'[Type])="XXO"&&MAX('Costcenter'[SubType])="VXO",0,[Capacity 2 measure])
RETURN _result

File download with all examples in it: click

[Ahmedx]

try

[MikeHendriks]

Looks awesome @Ahmedx . But can you explain what's going here? What are those magic '80' numbers doing there?

[Ahmedx]

you wrote the condition, If VXO = 80 and NXO is not present -> 80
I understand that if the conditions are met, the result should be 80

[MikeHendriks]

You are 100% right @Ahmedx, but you can also say:

If VXO = 70 and NXO is not present -> 70
If VXO is not present and NXO = 70 -> 70
If VXO = 70 and NXO = 70 -> 70

or

If VXO = 10000 and NXO is not present -> 10000
If VXO is not present and NXO = 10000 -> 10000
If VXO = 10000 and NXO = 10000 -> 10000

So in general I just wanted to state that if on ONE 'Costcenter'[Number] you have ánd NXO ánd VXO the amount must be divided by 2. And if only NXO ór VXO is present then you need that number.

But the outcome of your query looks ok, but I can't imagine that that 80 is necessary.

I hope I'm more clear now!?

[Ahmedx]

I get it, you can just write it like that

[MikeHendriks]

So close @Ahmedx.... I really appreciate your help!

But filtering on 'number' gives issues. Do you have an idea for this as well:

[Ahmedx]

try again

[Ahmedx · Accepted solution]

or try this

[MikeHendriks]

Amazing @Ahmedx. Thank you very much!

[Ahmedx]

pls try

[MikeHendriks]

Sadly I can't change the URL. But it seems to be removed after a download. This URL keeps alive: https://easyupload.io/u2jijx

[Ahmedx]

no file