Compute a daily average into a calculated column

[Anonymous]

Hello Everyone,

I'm having a bad time trying to solve this situation.

For some reason, I need to compute a measure into a calculated column. To sum up, I have many different measures computed based on different data tables and that are not related. But I need to display them within the same visual as KPIs; that would appear and be computed only if there is a result to show (and not display a "blank" measure). Measures are great but I can't use them directly as I would like through the current visuals and features that Power BI provides;
- With a multi-row card, I can't filter based on the value of the measure by removing "blanks" (only works with columns)

- With a table, there is no way to display the measures per row instead of column

- With a matrix, it doesn't work because I'm working with different measures from different tables
- With KPI or Gauge visual, it only allows us to display one measure at a time; that doesn't correspond to what I need.

Therefore I would like to compute the measures through a calculated column, and then use a multi-row card built on the calculated column on which I should be able to filter. Below an example:

Here the data table and the columns I need for one the measure, I want to compute a daily total number of cases. For each row with the same date; the same total amount will be displayed.

Here the formula that I have tried but are not working:

 This one make the entire sum of the column [Cases] without considering the date.

This one gives totally different results according to the row; and I have no idea why.

Could you help me with this, please?

Thanks,

Mailys

 

[Anonymous · Accepted solution]

Hi @Anonymous,

AFAIK, current power bi not able to create a dynamic calculate column/table based on filter/slicer. They are stored in different data levels and not able to interact from slicer/filter. 

I'd like to suggest creating a measure formula to instead, its result can dynamic changes based on filtered row contents.

Notice:  data levels (from parent to child, child level not able to affect the higher level)

database -> query table -> data model table(calculate column/table) -> data view(measure, fitler,slcier, visauls)

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you!

I understand what is possible and what is not now; the hierarchy level is clear.

This is not a solution but it helps! I found another way, involving measures indeed to make appear what I was looking for; created the measures, and then created a table of measures (with formulas UNION and ROWS). With only the measures, I couldn't reach my goal.

[parry2k]

@Anonymous not fully clear what you are trying to achieve. it is easy to understand if you share sample data and expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490