Visualisation

[Anonymous]

Hi all,

This is my Matrix visual. Is it possible to create a column in Matrix visual?

I would like to create a calculated column, the total of each part number to be divided by Part count (907). 

I cannot do that in the data field as the view of the data is different.

Any help would be appreciated!

Thank you!

Megha

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Try it.

Day Count =
CALCULATE (
    DISTINCTCOUNT ( 'Demand history & VVA'[DemandDate] ),
    ALL ( 'Demand history & VVA' )
)

Best Regards,
Community Support Team_ Yalan Wu

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create a measure add it to matrix ,then adjust the spacing of visual fields.

1.create a measure.

AVERAGE Daily = DIVIDE( SUM([Value]),COUNT('Table'[Cate]))

2.off these funtions.

3.then Manual spacing adjustment.

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yalanwu-msft Thank you so much!

I just have an other query.

Average Daily Use = DIVIDE(SUM('Demand history & VVA'[Quantity_1]),DISTINCTCOUNT('Demand history & VVA'[PartNumber]))

Why is the Average daily Use repeating what's in Total of Quantity_1?

How can I correct that?

Thank you!

Megha

[v-yalanwu-msft]

Hi, @Anonymous ;

You could try modify it.

Average Daily Use =
DIVIDE (
    SUM ( 'Demand history & VVA'[Quantity_1] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Demand history & VVA'[PartNumber] ),
        ALL ( 'Demand history & VVA' )
    )
)

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you! @v-yalanwu-msft 

But there was a small mistake in the formula I had put.

The formula I am using now is given below:

Average Daily Use = DIVIDE (SUM ( 'Demand history & VVA'[Quantity_1] ), 90)

But the same formula if I write it as 

Average Daily Use = DIVIDE (SUM ( 'Demand history & VVA'[Quantity_1] ), [Day Count]) )

I dont get the right answer.

Can I know why's that?

[Day Count] is a measure I created.

Thank you!

Megha

[v-yalanwu-msft]

Hi, @Anonymous ；

Can you share the formula for [Day Count] and the result returned?Or a simple data after removing the sesentive information?
According to the information you provided, I cannot accurately judge the reason for the error, but I guess it is caused by the context. Maybe you need to add ALLSELECTED() or ALL () to your count. Could you try the following or provide more information?
Looking forward to your reply ！

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yalanwu-msft,

The formula for DayCount is 

Day Count = DISTINCTCOUNT('Demand history & VVA'[DemandDate]). 

The result returned is 90.

Thank you!

Megha

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Try it.

Day Count =
CALCULATE (
    DISTINCTCOUNT ( 'Demand history & VVA'[DemandDate] ),
    ALL ( 'Demand history & VVA' )
)

Best Regards,
Community Support Team_ Yalan Wu

[Anonymous]

Hi @v-yalanwu-msft ,

On similar basis, this is the formula I have used for working days.

Working days = CALCULATE(

DISTINCTCOUNT('Demand history & VVA'[DemandDate].[Date]),

FILTER(

'Demand history & VVA',

'Demand history & VVA'[IsWorkingDay] = "TRUE"

))

the result returned is 65.

Now, I need to calculate Average daily usage.

The formula for that is:

Average Daily Use = DIVIDE([Total Volume],[Working days]).

But,

I get the right answer only when I give the formula as: 

Average Daily Use = DIVIDE([Total Volume],65).

Can I please know how to correct that? I would like to use the measure [working days].

Thank you!

Megha

[Anonymous · Accepted solution]

Hi @v-yalanwu-msft 

Thank you so much!🙂

[vanessafvg]

can you share some data in text format?  

can you also demonstrate what you expect to end up with via an example?

[Anonymous]

Hi @vanessafvg,

I have attached the excel sheet. I am expecting like this.

The column Average daily use is derived by dividing volume by the part number count.

i.e., in the first row, it is 3288.87/978. 

Is it possible in Matrix visual?

[vanessafvg]

Hi you haven't shared the data in text format, thats another screenshot.

[Anonymous]

Apologies!

Attaching in text format.

There are still a lot of date columns. It wasnt possible to copy the whole. The column 'Volume' shows the sum of all the dates.  

12/26/2021	12/27/2021	12/28/2021	12/29/2021		Volume	Average Daily Use
48	33.34605	33.34605	0		3288.87	36.54296
0.42409	0.42409	0	0		93.16	1.03508
0.18024	0	0	0		91.98	1.02198
48	1956	828	0		392375.00	4359.72222
0	0	0	0		0.00	0.00000
0	0	0	0		77.69	0.86321
0	0	0	0		4.89	0.05429

Thank you!

Megha