Create a complicated measure

[barlevitzky]

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. 

[BILASolution]

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]

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

[v-yuezhe-msft]

@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]

Hi @v-yuezhe-msft,

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

[v-yuezhe-msft]

@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]

Hi, @v-yuezhe-msft

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.  

[v-yuezhe-msft]

@barlevitzky,

Please check the DAX I use in this PBIX file, if you still get error message, please share me your PBIX file.

Regards,
Lydia

[barlevitzky]

@v-yuezhe-msft

I Click on download and nothing happen. 

Can you please send it to my email?

barlevitzky@gmail.com

Thanks

[v-yuezhe-msft]

@barlevitzky,

Your email provider rejects my message. Please check if you can download my attached PBIX file in this reply.

Regards,
Lydia

[v-yuezhe-msft]

@barlevitzky,

I send the PBIX file to you via Email.

Regards,
Lydia

[v-yuezhe-msft]

@barlevitzky,

Please post the DAX you use, as the error message mentioned, you pass two few arguments to ALLEXCEPT function.



Regards,
Lydia

[barlevitzky]

Hi @v-yuezhe-msft,

Thank you for your help!

this is the DAX i wrote:

[Ashish_Mathur · Accepted solution]

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]

Hi @Ashish_Mathur,

"Revenue per partner" is the table name and "test" is the column name. 

[Ashish_Mathur]

Hi,

So it should be:

ALLEXCEPT('Revenue per partner','Revenue per partner'[test])

[barlevitzky]

Sorry, the same error message is still on. 

[Ashish_Mathur]

Hi,

Considering Lydia wrote the formula for you initially (which was even marked as Answer), it would be best if she helps you with this.  For me to help, you I'd have to request you to explain me the entire question (which will waste your time).