LOD Expression from Tableau

[mikey268]

hi,

I want to create a calculation on top of a LOD expression similar to this.

MAXSALES = {Fixed Country : MAX(SALES)} which im doing by

MAXSALES = CALCULATE(MAX('Table1''Sales'),ALLEXCEPT('Table1',Table1[country]))

I want to SUM(MAXSALES) and use it in another calculation to achieve sum(individual sales) / SUM(MAXSALES)

Can someone please help.

Thanks in Advance.

[camargos88]

Hi @mikey268 ,

You can use CALCULATE(MAX('Table1''Sales'),ALL('Table1')), it will get the max value for this table.

Howerver, it's better if you provide a sample data. So we can check it better.

Ricardo

[mikey268]

Hi here is the context.

I want to write this in power bi:

SUM({FIXED DIM1, DIM2 : MAX(measure)})

[camargos88]

Hi @mikey268 ,

I would say you need to aggregate first with summarize and sum them.

Can you give some example about it ? 

[mikey268]

Hi, thanks for the response. something like this. I would do SUM({FIXED Quarter, Part : MAX(cost)}

Quarter	Part	Cost
2020Q1	A	100
2020Q1	A	100
2020Q1	A	100
2020Q1	B	200
2020Q1	B	200
2020Q1	B	200
2020Q1	B	200
2020Q1	C	300
2020Q1	C	300
2020Q1	C	300
2020Q2	A	200
2020Q2	A	200
2020Q2	A	200
2020Q2	B	100
2020Q2	B	100
2020Q2	B	100
2020Q2	C	400
2020Q2	C	400
2020Q2	C	400

Result:

Result	Part Cost
2020Q1	600
2020Q2	700

[camargos88 · Accepted solution]

Hi @mikey268 ,

Try this:

Measure = SUMX(SUMMARIZE('Table'; 'Table'[Quarter]; 'Table'[Part]; "Total"; MAX('Table'[Cost])); [Total])