Forum Discussion

barlevitzky's avatar
barlevitzky
Helper I
8 years ago
Solved

Create a complicated measure

Hi all, I have the following table: Date  Browser  Cost  Test 26.10 CH            5       A 26.10 FF             10     A 26.10 CH            10     B 26.10 FF             10     B   I want t...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi barlevitzky,

     

    You are missing the second input in the ALLEXCEPT function.  The first is the table which you have given.  The second input should be the column.

barlevitzky's avatar
barlevitzky
Helper I
8 years ago

Hi BILASolution, 

Thanks for your answer!

First, I have Page and report filters, so "ALL" Function is disabling it right?

I tried to simplify my explanation above, but I have several "Test A", for example, "TestA1" and "TestA2" and so on. 

I tried this DAX: 

Total B = CALCULATE([Total Rev],'Revenue per partner'[Test] = "Variant B -  (4146)",'Revenue per partner'[Test] = "Variant B -  (4443)",'Revenue per partner'[Test] = "Variant B -  (5141)", 'Revenue per partner'[Test] = "Variant B -  (5924)",'Revenue per partner'[Test]= "Variant B -  (5933)") 

but it returns empty cells. 

 

In Other words,

I have managed to create 2 subtotals without the general total. 

but now I want to create a new measure Subtotal A / Subtotal B . 

 

Thanks

Anonymous's avatar
Anonymous
Not applicable
8 years ago

barlevitzky,

Firstly, ALL function ignores all slicers and Visual/Page/Report filters.

Secondly, please post complete data of your table and post expected result here.

Regards,
Lydia

  • barlevitzky's avatar
    barlevitzky
    Helper I
    8 years ago

    Hi Anonymous,

     

    The data looks like this

     

    :

     

    The expected result:

     

    For every string of Date+Device+Engine+Geo, I have 2 Variants so I am using a slicer for Device+Engine+Geo. 

     

    I am trying to create the column of Total D / Total C . 

     

    Thanks

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      barlevitzky,

      How do you get 12 for partner B under Variant C? You also have Variant B. Based on your sample data, I creating the following measures and get the following result.

      Total C = CALCULATE(SUM(Table[Revenue]),ALLEXCEPT(Table,Table[Test]),Table[Test]="Variant C")
      Total D = CALCULATE(SUM(Table[Revenue]),ALLEXCEPT(Table,Table[Test]),Table[Test]="Variant D")
      TOTAL D/C = [Total D]/[Total C]


      Regards,
      Lydia

      • barlevitzky's avatar
        barlevitzky
        Helper I
        8 years ago

        Hi, Anonymous

         

        I got an error message: "Too few arguments were passed to ALL EXCEPT function. The minimum argument count for the function is 2. " 

         

        I don't get it. I have in each Variant more than 2 rows. 

         

        Thanks.