Forum Discussion

barlevitzky's avatar
barlevitzky
Icon for Helper I rankHelper 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 to create a view(table) that will look this:

           Test A                                               Test B

Date  CH FF  *Sum of test A Cost*  CH FF *Sum of test B cost*     **Measure (Sum of B / Sum of A)**

26.10  5   10           15                         10 10                20                                   1.33 (20/15)

 

I am trying to create the measures that marked with *.

 

Browser column has more than 2 type of values. 

 

Thanks in advance. 

 

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.

17 Replies

  • BILASolution's avatar
    BILASolution
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

    Hi barlevitzky

     

    Try this measures...

     

    Total Cost = SUM(Browser[Cost]) 
    Total Cost A = CALCULATE([Total Cost];ALL(Browser);Browser[Test] = "A")
    Total Cost B = CALCULATE([Total Cost];ALL(Browser);Browser[Test] = "B")
    SUM B / SUM A = DIVIDE([Total Cost B];[Total Cost A])

    NOTE: My table is called "Browser"

     

    ...and this is the final result

     

     

     

     

    Regards

    BILASolution

     

    • barlevitzky's avatar
      barlevitzky
      Icon for Helper I rankHelper 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