DAX measure

[Anonymous]

Hi,
Through an earlier query, I was helped to create a Derived table using 'matrix' visualization. apologies that I should have asked that I really needed exact help on - I am calculating % Progress as - (Status1+Status2)/Grand Total. I am not sure how to get the DAX for creating this % Progress column/measure. Can you help?

Base Table			Derived Table
Groups	Work Status	Work Id		Count of Work Id	Column Labels			Measure?
abc	Status1	1		Row Labels	Status1	Status2	Status3	Grand Total	% Progress
abc	Status2	2		abc	1	1		2	100%
def	Status2	3		def		2	1	3	67%
def	Status2	4		Grand Total	1	3	1	5	80%
def	Status3	5

[Anonymous]

Hi,May be I was not clear. Let me explain

Base Table - left side     Derived Table-right side

Groups	Work Status	Work Id							Measure?
abc	Status1	1		Groups(from Base Table)	Status1(Per Groups count)	Status2(Per Groups count)	Status3(Per Groups count)	Grand Total*	% Progress
abc	Status2	2		abc	1	1		2	100%
def	Status2	3		def		2	1	3	67%
def	Status2	4		Grand Total	1	3	1	5	80%
def	Status3	5

Derived Table (Only available as Excel pivot and I can draw a matrix visual upto Grand Total*)

Grand Total* = Status 1 + Status2 + Status3 (based on Groups, you can observe)

I'm thinking of a DAX Measure which calculates (status1+staus2)/Grand Total* and grouped by Groups -just thinking on these lines, so that I can include '%Progress' as another column by the side of Grand Total*?

Thanks for all your support

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Kindly try my last DAX which I suggested.

Thanks,

Pragtai

[Anonymous]

Thanks a lot Pragati, this works fine. Regards,

[Pragati11]

Hi @Anonymous ,

Is STATUS1, STATUS2 and GRAND TOTAL separate columns in your dataset or they are calculated measures?

If these are 3 measures, just create another measure as follows:

Progress% = DIVIDE(([Status1] + [Status2]), [Grand Total], 0)

The chnage the dattype of  Progress% to percentage.

Thanks,

Pragati

[Anonymous]

Hi Pragati, But Status1 and Status2 are actually row values from my base table and grouped by Groups column. I sense the query has to include the reference to base table and have something like group by/filter by 'Groups' may be?

[Pragati11]

Hi @Anonymous ,

Therefore I asked you whether they are columns or not as you havenot mentioned any data structure in your query for your original dataset.

Also what does GRAND TOTAL represents? If I tale GRAND TOTAL as rows in the dataset, then you can write following measure:

Progress%=

var st1 = CALCULATE(COUNTROWS(tablename), tablename[WorkStatus] = "Status1")

var st2 = CALCULATE(COUNTROWS(tablename), tablename[WorkStatus] = "Status2")

var totRows = COUNTROWS(tablename)

RETURN
DIVIDE((st1 + st2), totRows, 0)

Replace "tablename" in above DAX with your table name.

Thanks,

Pragati