Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Grouping by calculated measures and drilldown date

Hi everyone,

 

I have a question. I have attached the dataset and the sample workbook in the link below for your reference. 

 

Sample Excel Dataset

Sample PowerBI pbix

 

The problem above mirrors the problems I have been facing desigining PowerBI dashboards at work. In short, I will detail what I have done here based on a sample PowerBI report using dummy data. 

 

 

pic.png

I am calculating the status of each company using a measure based on the sum of values during each period divided by number of days during the time period of date drilldown. As the status will change with the granularity of the date hierarchy , I can't possibly put as a column as it is static. So for each date hierarchy, the values will be aggregated accordingly. Then I write another measure that will classify the status (either Gold, Silver, Bronze and Ordinary) according to their aggregated value. You might want to visit my sample PowerBI report if the above explanation is not concise.

 

However, I want to count the workshops, group by their status and their date range.

 

I show an example of the outcome I would like to have below. The numbers are basically distinct count of companies that are of the status in that particular month (or date granularity). Is there a way to make the status I have calculated on the rows of the matrix (since the rows have to be populated by columns)?

 

pic2.PNG

 

I would like to seek the advice of the community on the best foot forward to achieve my desired outcome.

 

Thank you very much in advance!

 

 

 

4 REPLIES 4
Anonymous
Not applicable

Hi everyone,

 

I have a question. I have attached the dataset and the sample workbook in the link below for your reference. 

 

Sample Excel Dataset

Sample PowerBI pbix

 

The problem above mirrors the problems I have been facing desigining PowerBI dashboards at work. In short, I will detail what I have done here based on a sample PowerBI report using dummy data. 

 

 

pic.png

I am calculating the status of each company using a measure based on the sum of values during each period divided by number of days during the time period of date drilldown. As the status will change with the granularity of the date hierarchy , I can't possibly put as a column as it is static. So for each date hierarchy, the values will be aggregated accordingly. Then I write another measure that will classify the status (either Gold, Silver, Bronze and Ordinary) according to their aggregated value. You might want to visit my sample PowerBI report if the above explanation is not concise.

 

However, I want to count the workshops, group by their status and their date range.

 

I show an example of the outcome I would like to have below. The numbers are basically distinct count of companies that are of the status in that particular month (or date granularity). Is there a way to make the status I have calculated on the rows of the matrix (since the rows have to be populated by columns)?

 

pic2.PNG

 

I would like to seek the advice of the community on the best foot forward to achieve my desired outcome.

 

Thank you very much in advance!

 

 

 

v-chuncz-msft
Community Support
Community Support

@Anonymous 

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Create-a-bar-graph-with-measures-as-axis/td-p/455900

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi. Anyone can help? I still can't mange to get it implemented on the workbook.

Anonymous
Not applicable

Thanks for the reply!

 

Unfortunately, I am still having problems following the link. I am struggling to comprehend the following DAX in the link provided below.

 

ClusteredSales :=
CALCULATE (
    SUM ( Sales[SalesAmount] ),
    FILTER (
        ADDCOLUMNS (
            Customers,
            "CustomerSales",
            CALCULATE (
                SUM ( Sales[SalesAmount] ),
                CALCULATETABLE ( Customers ),
                ALLSELECTED ()
            )
        ),
        COUNTROWS (
            FILTER (
                'Clusters',
                [CustomerSales] >= 'Clusters'[Min Sales]
                && [CustomerSales] < 'Clusters'[Max Sales]
            )
        ) > 0
    )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.