Forum Discussion

MikeHendriks's avatar
MikeHendriks
Icon for Helper I rankHelper I
2 years ago
Solved

Measure based on dividing on types

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 specia...
Need Help
MikeHendriks's avatar
MikeHendriks
Icon for Helper I rankHelper I
2 years ago

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

  • MikeHendriks's avatar
    MikeHendriks
    Icon for Helper I rankHelper I
    2 years ago

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

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    MikeHendriks
    Icon for Helper I rankHelper I
    2 years ago

    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!?

  • MikeHendriks's avatar
    MikeHendriks
    Icon for Helper I rankHelper I
    2 years ago

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

     

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