Hi,
I have a project where I'm converting excel forecast model into pbi. Below is my expected output combined using two tables :
| PRODUCT | JAN | FEB | MAR | APR | JUN | Total |
| # PRODUCTS DEPLOYED | 100 | 150 | 200 | 250 | 300 | 1000 |
| # BILLED | 75 | 100 | 150 | 200 | 250 | 775 |
| # PAID | 50 | 75 | 100 | 150 | 200 | 575 |
| % of billed | 75% | 67% | 75% | 80% | 83% | 78% |
| % of paid | 50% | 50% | 50% | 60% | 67% | 58% |
| % paid from billed | 67% | 75% | 67% | 75% | 80% | 74% |
In the above example I'm trying to calaculate the % based on how many were delpoyed were billed , deployed were paid, billed to paid. Below are the formulas :
%Billed = # of billed/ # Products deployed
%Paid= # of paid/ # Products deployed
% paid from billed = # of paid/ # of billed
SAMPLE DATA :
Table 1 :
| PRODUCT | ID |
| # PRODUCTS DEPLOYED | SDFC1 |
| # PRODUCTS DEPLOYED | SDFC2 |
| # PRODUCTS DEPLOYED | SDFC3 |
| # PRODUCTS DEPLOYED | SDFC4 |
| # PRODUCTS DEPLOYED | SDFC5 |
| # PRODUCTS DEPLOYED | SDFC6 |
| # BILLED | SDFC1 |
| # BILLED | SDFC2 |
| # BILLED | SDFC3 |
| # PAID | SDFC1 |
| # PAID | SDFC2 |
Table 2:
| DATE | ID |
| JAN | SDFC1 |
| JAN | SDFC2 |
| FEB | SDFC3 |
| FEB | SDFC4 |
| MAR | SDFC5 |
| APR | SDFC6 |
I'm trying to achieve this using Matrix visual in power bi.
I achieved calculating %Billed with below two measures . Not sure how to add two more calacualtions %Paid and % paid from billed.
[Measure_1] =DISTINCTCOUNT('Table1'[ID])
[Measure_2] =
if(HASONEFILTER('Table1'[ PRODUCT ]),
FORMAT([Measure_1],"General Number"), FORMAT(CALCULATE([Measure_1],'Table1'[ PRODUCT ]="# BILLED ")
/
CALCULATE([Measure_1],''Table1'[ PRODUCT ]="# PRODUCTS DEPLOYED"),"Percent")
)
Any help and guidance is highly appreciated.
Thanks
