Forum Discussion

Jensk's avatar
Jensk
Frequent Visitor
2 years ago
Solved

Static and dynamic calculations with same slicing

I have this test data:

EntityCompanyCustomerAmountPrice
Entity 1Company1Customer11766,95
Entity 1Company1Customer249249
Entity 1Company2Customer38227,35
Entity 1Company2Customer446385,74
Entity 2Company3Customer529592,05
Entity 2Company3Customer63020,29
Entity 2Company4Customer746891,46
Entity 2Company4Customer824822,71
Entity 1Company1Customer118190,09
Entity 1Company1Customer220750,22
Entity 1Company2Customer38283,53
Entity 1Company2Customer431146,72
Entity 2Company3Customer544871,95
Entity 2Company3Customer630123,9
Entity 2Company4Customer743936,6
Entity 2Company4Customer814839,91
Entity 1Company1Customer127802,02
Entity 1Company1Customer221393,18
Entity 1Company2Customer334650,46
Entity 1Company2Customer445930,55
Entity 2Company3Customer55959,85
Entity 2Company3Customer627709,72
Entity 2Company4Customer742356,27
Entity 2Company4Customer837945,8
Entity 1Company1Customer135919,21
Entity 1Company1Customer213885,57
Entity 1Company2Customer328682,22
Entity 1Company2Customer44398,22
Entity 2Company3Customer510757,28
Entity 2Company3Customer633855,14
Entity 2Company4Customer721316,09
Entity 2Company4Customer89909,33
Entity 1Company1Customer19195,81
Entity 1Company1Customer230205,67
Entity 1Company2Customer310562,07
Entity 1Company2Customer410229,25
Entity 2Company3Customer51438,52
Entity 2Company3Customer632179,08
Entity 2Company4Customer74625,6
Entity 2Company4Customer848386,29
Entity 1Company1Customer12714,3
Entity 1Company1Customer221873,84
Entity 1Company2Customer347381,9
Entity 1Company2Customer434293,29
Entity 2Company3Customer543245,75
Entity 2Company3Customer613882,22
Entity 2Company4Customer710126,71
Entity 2Company4Customer88855,36
Entity 1Company1Customer138587,44
Entity 1Company1Customer241401,24
Entity 1Company2Customer33368,76
Entity 1Company2Customer450263,24
Entity 2Company3Customer544493,07
Entity 2Company3Customer61556,46
Entity 2Company4Customer729664,4
Entity 2Company4Customer82839,34
Entity 1Company1Customer127965
Entity 1Company1Customer22817,39
Entity 1Company2Customer334803,59
Entity 1Company2Customer43898,96
Entity 2Company3Customer55312,12
Entity 2Company3Customer68123,65
Entity 2Company4Customer730360,22
Entity 2Company4 Customer842773,32

 

I need to slice by a hierarchy of entities that consists of companies that each have their customers. I can look at numbers for the Entity or for a Company within the Entity:

 

My Measures:

 

 

Revenue = SUMX( Sales, Sales[Amount] * Sales[Price] )
% of Total Revenue = DIVIDE( [Revenue], CALCULATE([Revenue],ALLSELECTED()))
Fixed Cost = 10000*[% of Total Revenue]

 

 

 I need the '% of Total Revenue' to be dynamic with my slicer and 'Fixed Cost' to be static (always 

10000*[% of Total Revenue] by Company.
 
So my fixed cost for Customer1 should always be 5510,54 regardless of slicing:
When I select the Entity my Fixed Cost should remain unchanged (Static at the Company level)

 

How do I change my Fixed Cost measure to be static while keeping my '% of Total Revenue' dynamic?
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    ALLSELECTED removes the company filter context, so try putting that back in explicitly like this:

    Fixed Cost =
10000
    * DIVIDE (
        [Revenue],
        CALCULATE ( [Revenue], ALLSELECTED (), VALUES ( Sales[Company] ) )
    )

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    ALLSELECTED removes the company filter context, so try putting that back in explicitly like this:

    Fixed Cost =
10000
    * DIVIDE (
        [Revenue],
        CALCULATE ( [Revenue], ALLSELECTED (), VALUES ( Sales[Company] ) )
    )
    • Jensk's avatar
      Jensk
      Frequent Visitor
      2 years ago

      Great, just what I needed. Thanks!