How to make a pie chart that count assingments

[ThomasWeppler]

Hi Power BI community
I have a tabel with a lot of assingments.

All of them are connected to an invoice tabel, an hour tabel and a consumpted items tabel.

Based on those three tabels I can calculated the profit margin on each assingment.

I want to make a pie chart that shows all the assingments with a high, medium and low profit margin.

The problem is that the profit margin on an assingment changes based on the date filter I choose, so I cannot just calculate the profit margin in my assingment tabel I need to use a measure.
But how do i make a meassure that count the all assingments with a profit marign per hour higher than 800, between 400 and 800 and bleow 400 that can be showed in a pie chart and respect the date filter?

[MohammadLoran25 · Accepted solution]

Hi @ThomasWeppler ,

Providing you with an exact answer requires a sample data. 

But I think the solution below would give you what you need:

1-Create a new Table as below (Let's name it RangeTable):

RangeID             RangeName           MinOfRange            MaxOfRange

     1                          0-400                     0                              400

     2                        400-800                   400                          800

     3                     800-1000000              800                          1000000

2-Create a DAX Measure Using CROSSJOIN as below:

CountOfAssignments =
CALCULATE (
    DISTINCTCOUNT ( AssignmentTable[AssignmentId] ),
    FILTER (
        CROSSJOIN ( AssignmentTable, RangeTable ),
        RangeTable[MinOfRange] < [Profit Margin Measure]
            && RangeTable[MaxOfRange] > [Profit Margin Measure]
    )
)

3-Then you just need to put [CountOfAssignments] measure and RangeName field in a pie chart.

If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.

Regards,

Loran

[ThomasWeppler]

Thanks @MohammadLoran25 for a great solution.

It worked right away.

I need to start to use CROSSJOIN more.

[MohammadLoran25]

Your're welcome @ThomasWeppler 

Yes sometimes it really helps but we also need to be careful in using CROSSJOIN as it might cause performance problems. If your data is large, the measure would be slow.

But there are solutions for that as well through performance optimization. (making the context which CROSSJOIN goes through smaller as much as possible)