topic Re: Measure Divide by column Value for 12 different month in DAX Commands and Tips

Measure Divide by column Value for 12 different month

Anonymous — Mon, 27 Apr 2020 11:57:12 GMT

Hello together,

first time posting here, cause i have nobody in my company to ask about power bi and DAX.

I calculated a filtert measure in my table for every month, which works fine, but now i need to divide this Measure for every month by a calculated number of days which come from another created Table.

SUMX(

FILTER('gf Effort';

'gf Effort'[MonthOfDate] IN {1});

[Gesamtleistung] / CALCULATE([Planarbeitstagesumme];Arbeitstage[MonthOfDate] IN {1}))

I can make it work for 1 month, but i dont want to calculate 12 different measure for every month.

So is there a solution to make this DAX expression work for every month, that i get a table or a row with the 12 values?

I'm using direct query for the data, which comes from a sql server.
The 'Arbeitstage' table is created inside power bi and the 'Planarbeitstagesumme' is a calculated Measure from 4 different rows inside the 'Arbeitstage' Table
The [Gesamtleistung] is a calculated Measure to, which filters a row inside the table 'gf effort'

Thanks.

Re: Measure Divide by column Value for 12 different month

Greg_Deckler — Mon, 27 Apr 2020 12:15:45 GMT

So normally you would have the "month" in the visual and then you could use something like:

VAR __Month = MAX('Table'[Month])

RETURN

SUMX(

FILTER('gf Effort';

'gf Effort'[MonthOfDate] __Month);

[Gesamtleistung] / CALCULATE([Planarbeitstagesumme];Arbeitstage[MonthOfDate] = __Month))

But really difficult to tell what exactly is going on. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Measure Divide by column Value for 12 different month

Anonymous — Tue, 28 Apr 2020 07:58:37 GMT

Well that would only give me the result for last Month (12) which is the max number.

[Arbeitstage]

MonthOfDate	Arbeitstage	Krankentage	Plantage
1	20	1	19
2	22	0,5	21,5
3	21	2	19

[gf effort]

MonthOfDate	..	..	Revenue
1	..	..	10000
2	...	..	7890
1	..	..	5000

...

these are my tables.

Now i calculate the [Gesamtleistung] with

Gesamtleistung = CALCULATE(SUM('gf Effort'[Revenue]); NOT('gf RevenueType'[Name] IN {"Noch nicht fakturierbare"}))

and this [Gesamtleistung] i want divide by the Plantage row for every Month.

Like,

1 = 15000/19

2 = 7890/21,5

...

VAR __Month = MAX('Table'[Month])

RETURN

SUMX(

FILTER('gf Effort';

'gf Effort'[MonthOfDate] __Month);

[Gesamtleistung] / CALCULATE([Planarbeitstagesumme];Arbeitstage[MonthOfDate] = __Month))

Only gives me the last one (12) because its the max Month

Thanks anyway.

Re: Measure Divide by column Value for 12 different month

Anonymous — Tue, 28 Apr 2020 09:01:31 GMT

I found a way to a solution.

SUMX('gf Effort'; CALCULATE(SUM('gf Effort'[Revenue]); NOT('gf RevenueType'[Name] IN {"Noch nicht fakturierbare"}))/SUMX(Arbeitstage;Arbeitstage[Planarbeitstagerechnung]))